Trong nhiều trường hợp phân tích nghiệp vụ, bạn phải tạo một bảng Date Dimension riêng dựa trên dữ liệu đang có hoặc dựa trên một khoảng thời gian cụ thể để đáp ứng nhu cầu thực tế. Bảng Date Dimension tạo thêm có thể cần bổ sung các trường thông tin ví dụ như năm tài chính, ngày làm việc hay chỉ để chỉnh gọn lại tên các tháng bằng cách viết tắt.
Với những đầu công việc đã nêu trên hoàn toàn có thể giải quyết được khi áp dụng DAX. Tại cuối bài viết này, KPIM cũng sẽ đưa ra cho các bạn một đoạn code mẫu bằng DAX giúp tạo ra bảng dữ liệu Date Dimension với đầy đủ các trường thông tin cần thiết.
Sử dụng CALENDAR vs CALENDARAUTO
Đầu tiên, để tạo ra một Date Dimension, trước tiên bạn phải tạo ra một bảng với mỗi dòng là các ngày chạy liên tiếp. Để làm được điều này, DAX có hai công thức cho bạn lựa chọn đó chính là CALENDAR và CALENDARAUTO. Và sau đây là công dụng của chúng.
CALENDAR
Hãy thử công thức CALENDAR dưới đây với hai tham số là ngày bắt đầu (Start_Date) và ngày kết thúc (End_Date):
CALENDAR(<start_date>,</end_date>)
Công thức DATE(year,month,day)
CALENDAR trả về cho bạn một bảng dữ liệu có một cột Date duy nhất là danh sách các ngày từ Start_Date đến End_Date. Bạn có thể dùng công thức DATE(year,month,day) để xác định ngày cụ thể hay sử dụng MIN(Date) và MAX(Date) để dựa vào ngày gần nhất, xa nhất trong bảng.
CALENDARAUTO
Tuy nhiên nếu bạn có nhiều trường Date trong bảng dữ liệu và muốn từ đó tạo bảng Date Dimension, thì CALENDARAUTO là lựa chọn hoàn hảo.
Công thức có dạng:
CALENDARAUTO([fiscal_year_end_month])
Với giá trị bỏ vào công thức fiscal_year_end_month là giá trị từ 1-12 biểu diễn tháng cuối của năm hoặc không để gì sẽ từ mặc định là tháng 12).
CALENDARAUTO() nhìn vào tất cả cả các trường dữ liệu dạng Date ở Data Model (không bao gồm trường tạo bởi DAX hoặc trường trong bảng tạo bởi DAX) để xác định ngày gần nhất (MAX Date) và ngày xa nhất (MIN Date); từ đó trả về bảng Date từ ngày đầu tiên trong năm của MIN Date (1/1) đến ngày cuối trong năm của MAX Date (31/12).
Ở ví dụ trên, chúng ta có 2 bảng trích xuất từ cơ sở dữ liệu là SalesOrder và OrderDetail. Đồng thời chúng ta cũng tạo một bảng Custom Date Dimension bằng DAX với Date range từ 1/1/2000 đến 31/12/2025. Các bạn có thể thấy khi sử dụng CALENDARAUTO, Power BI chỉ nhìn vào 2 bảng SalesOrder và OrderDetail, từ đó xác định khoảng thời gian từ 1/1/2011 đến 12/31/2014.
Sử dụng ADDCOLUMNS để thêm trường
Cả hai công thức trên là tiền đề để tạo ra các bảng Date Dimension, tuy nhiên chúng ta có thể bổ sung nhiều thông tin hữu ích hơn nữa trong bảng Date Dimension này. Ví dụ, chúng ta có thể bổ sung các tầng dữ liệu khác như ngày, tháng, năm hoặc các tuần trong tháng, tuần trong năm cũng như thông tin về năm tài chính (Fiscal Year) nếu nó không đồng nhất với năm dương lịch (Calendar Year) hoặc xác định ngày trong tuần, ngày làm việc,…
Sau khi tạo ra danh sách Date bằng 2 công thức trên, chúng ta có thể dễ dạng tạo thêm các Calculated Columns từ cột đầu tiên [DATE] này để tính toán và bổ sung các trường liên quan khác. Và còn tiện hơn, khi bạn hoàn toàn có thể làm điều này cùng lúc với tạo bảng DATE trên DAX. Bằng việc bổ sung ADDCOLUMNS, chúng ta có thể tạo một bảng cùng các trường thông tin mới một cách dễ dàng bằng 1 công thức DAX.
Công thức của ADDCOLUMNS như sau: Ví dụ bổ sung thêm năm, tháng và ngày trong bảng Date Dimension:
Date = ADDCOLUMNS(
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"Year", YEAR( [Date] ),
"Month", MONTH( [Date] ),
"Day", DAY( [Date])
)
Một số trường hữu dụng và công thức để bổ sung
Trả về quý, tháng, tuần, ngày
| Trường áp dụng | Kết quả trả về |
|---|---|
QUARTER([Date]) |
Số quý trong năm (1 - 4) |
FORMAT([DATE], "q") |
Số quý trong năm (1 - 4) |
MONTH([Date]) |
Số tháng (1 - 12) |
FORMAT([DATE], "mm") |
Số tháng (1 - 12) |
FORMAT([DATE], "mmm") |
Tên tháng (Jan - Dec) |
FORMAT([DATE], "mmmm") |
Tên tháng (January - December) |
WEEKNUM([DATE]) |
Số tuần trong năm (1 - 54) |
FORMAT([DATE], "ww") |
Số tuần trong năm (1 - 54) |
DAY([DATE]) |
Số ngày trong tháng (1 - 31) |
FORMAT([DATE], "d") |
Số ngày trong tháng (1 - 31) |
FORMAT([DATE], "ddd") |
Số ngày trong tuần (Sun - Sat) |
Xác định năm tài chính (Fiscal Year) với tháng cuối khác 12 và cách sort tháng chuẩn
Bạn có thể áp dụng công thức sau và thay số 6 ở ví dụ dưới đây bằng tháng cuối của năm tài chính của bạn.
| Công thức | Kết quả trả về |
|---|---|
IF(Month <= 6, Year, Year +1) |
Số năm tài chính |
IF(Month <= 6, Month + 6, Month – 6) |
Số sort cho tháng theo đúng thứ tự trong năm tài chính |
Các bạn chú ý để sort tên tháng chuẩn (vì thứ tự tháng bị đảo lộn khi tháng cuối năm là tháng khác 12), các bạn có thể tạo trường mới để sort như trên để được kết quá như dưới:
Xác định ngày làm việc (Working Days)
Ví dụ công ty bạn có ngày làm việc trong tuần từ thứ 2 đến thứ 7, chúng ta có thể bổ sung trường WEEKDAY hoặc dùng WEEKDAY để xác định ngày nghỉ là CN.
Với công thức mặc định WEEKDAY([DATE]) sẽ trả về giá trị 1 cho Chủ Nhật và 2 – 7 là thứ hai đến thứ bảy.
| Công thức | Kết quả trả về |
|---|---|
WEEKDAY([DATE]) |
Ngày trong tuần số 1 - 7 |
IF(WEEKDAY([DATE]) = 1, "No", "Yes") |
Working Day: "Yes" hoặc "No" |
Công thức DAX mẫu
Date Auto = ADDCOLUMNS(
CALENDAR(DATE(2000,1,1),DATE(2025,12,31)),
"Year", YEAR([Date]),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"Month", MONTH([Date]),
"Month Short", FORMAT ( [Date], "mmm" ),
"Week", FORMAT ( [Date], "ww" ),
"WeekDay", WEEKDAY([Date]),
"Day", DAY([Date]),
"Day Short", FORMAT ( [Date], "ddd" ),
"Fiscal Year","FY"& IF(MONTH([Date]) <=6, Year([Date]), YEAR([Date])+1),
"Month Fiscal sort", IF(MONTH([Date]) <=6, MONTH([Date])+6,MONTH([Date])-6),
"Working Date", IF(WEEKDAY([Date])=1, "No", "Yes")
)
Và để tổ hợp lại, mình muốn chia sẻ cho mọi người một đoạn DAX mẫu mình chuyên sử dụng trong các báo cáo. Mọi người có thể lưu lại trong file text hoặc Power BI nào đó để tiện sử dụng.
Hi vọng bài viết này giúp các bạn hiểu rõ hơn cách tạo bảng Custom Date Dimension bằng DAX. Hãy đón đọc bài tiếp theo về cách tạo bảng Date tương tự bằng M trong Power Query nhé. Hẹn gặp lại!
