DAX CƠ BẢN: Hàm TOTALYTD, TOTALQTD, TOTALMTD – CÁCH SỬ DỤNG VÀ VÍ DỤ CỤ THỂ

Trong Power BI, có rất nhiều hàm DAX hiệu chỉnh bộ lọc về thời gian (Ví dụ như: DATESINPERIOD, DATESBETWEEN, DATEADD,… hay còn gọi là hàm DAX Time Intelligence, mỗi hàm phục vụ cho một mục đích hay một bài toán cụ thể . 

Các hàm TOTALYTD/ TOTALQTD /TOTALMTD (ở đây mình tạm gọi là nhóm hàm TOTAL TO DATE) là nhóm hàm vô cùng hữu dụng giúp chúng ta tính toán được chỉ tiêu lũy kế từ đầu năm (YTD), đầu quý (QTD) hay đầu tháng (MTD) cho đến hiện tại (To Date có nghĩa là tính giá trị đó cho đến hiện tại). Ngoài ra, hàm TOTALYTD còn hỗ trợ thay đổi kỳ tính toán để phù hợp với năm tài chính tùy vào công ty, quốc gia.

Trong bài viết này, Datapot sẽ tìm hiểu cách sử dụng nhóm hàm TOTAL TO DATE (TOTALYTD, TOTALQTD, TOTALMTD) trong POWER BI và đi qua một vài ví dụ cụ thể để hiểu rõ hơn về cách hoạt động của nhóm hàm này. Bên cạnh đó, Datapot sẽ cùng bạn xây dựng công thức tính lũy kế theo tuần (TOTALWTD) khi POWER BI chưa cung cấp trực tiếp công thức của hàm này. Ở phía cuối bài viết sẽ có đính kèm phần POWER BI để các bạn có thể tương tác trực tiếp.

Việc nắm rõ mục đích, cú pháp và cách hoạt động của nhóm hàm TOTAL TO DATE sẽ giúp các bạn nâng cao khả năng viết DAX, áp dụng trực tiếp vào công việc thực tế và nâng cao hiệu quả công việc. 

MỤC ĐÍCH VÀ CÚ PHÁP CỦA HÀM TOTALYTD, TOTALQTD, TOTALMTD

TOTALYTD

• MỤC ĐÍCH

TOTALYT (TOTAL YEAR TO DATE) giúp chúng ta tính tổng của một chỉ tiêu hay một chỉ số nào đó từ đầu năm cho tới hiện tại.

Việc tính lũy kế năm sẽ chỉ có khi chúng ta quan sát dữ liệu ở dưới level năm việc này giúp người làm phân tích dữ liệu hay các nhà quản lý có thể theo dõi doanh thu dồn tích, tích lũy qua hàng quý, hàng tháng. Từ đó có thể phát hiện ra sự bất thường trong quá trình hoạt động kinh doanh, sản xuất của doanh nghiệp

Lưu ý: hàm TOTALYTD có thể sử dụng để tính lũy kế theo năm tài chính (Financial year) của công ty.

Ví dụ năm tài chính tại một số nước :

• CÚ PHÁP

TOTALYTD(<expression>,<dates>,[<filter>],[<year_end_date>])

Giải thích:

TOTALQTD

• MỤC ĐÍCH:

TOTALQTD (TOTAL QUARTER TO DATE) giúp chúng ta tính tổng của một chỉ tiêu hay một chỉ số nào đó từ đầu quý cho tới hiện tại.

Giá trị lũy kế theo quý chỉ có ý nghĩa khi quan sát dưới level quý.

Lưu ý: Hàm TOTALQTD() mặc định kết thúc quý như quý dương lịch.

• CÚ PHÁP

TOTALQTD(<expression>,<dates>[,<filter>])

Giải thích:

TOTALMTD

• MỤC ĐÍCH

TOTALMTD (TOTAL MONTH TO DATE) giúp chúng ta tính tổng của một chỉ tiêu hay một chỉ số nào đó từ đầu tháng cho tới hiện tại.

Tương tự hàm TOTALQTD(), hàm TOTALMTD() – chỉ số sẽ chỉ có ý nghĩa khi chúng ta quan sát dữ liệu theo level tháng (ngày, tuần,…), cách tính lũy kế tháng thường được sử dụng đối với những doanh nghiệp cần theo dõi các chỉ tiêu trong khoảng thời gian nhỏ, từ đó tìm ra insight cho doanh nghiệp.

• CÚ PHÁP

TOTALMTD(<expression>,<dates>[,<filter>])

Giải thích:

Lưu ý: hàm TOTALMTD mặc định thời gian kết thúc tháng như tháng dương lịch.

ỨNG DỤNG CỦA NHÓM HÀM

Ở phần này, hãy cùng Datapot đi sâu vào nhóm hàm, cách câu lênh thực thi cũng như giá trị trả về của từng hàm TOTALYTD, TOTALQTD, TOTALMTD.

Nhóm hàm TOTAL TO DATE có mục đích sử dụng là tính lũy kế của một chỉ tiêu nhất định theo từng loại kỳ nhất định ( Năm, Tháng, Ngày) vậy nên các bạn nên sử dụng để tính toán với các chỉ tiêu CÓ THỂ CỘNG DỒN theo kỳ như :

• Doanh thu
• Lợi nhuân
• Số lượng khách hàng mới
• Số lượng đơn hàng
• Số lượng sản phẩm bán ra
• Số giờ làm việc
• ….

Data set thực hành gồm:

• Bảng Sale: gồm thông tin giao dịch của
• Bảng Date: cách tạo bảng date.

• TOTALYTD():

Ví dụ 1: Tính doanh thu lũy kế cho từng năm, tạo biểu đồ doanh thu lũy kế theo từng năm, (năm tài chính đặt mặc định là năm dương lịch).

Công thức DAX:

Doanh thu = SUM(Sales[Total Amount])

Doanh thu lũy kế năm = TOTALYTD(SUM(Sales[Total Amount]),'Date'[Date])

Trong đó:

• SUM(Sales[Total Amount]): Tính tổng doanh thu
• ‘Date'[Date]: Cột date của dữ liệu
• Năm tài chính mặc định là năm dương lịch nên ta không cần điền tham số [<year_end_date>]

Matrix table gồm:

• Cột Date.
• Doanh thu.
• Doanh thu lũy kế năm.

Như chúng ta có thể thấy doanh thu sẽ được tính theo lũy kế năm, khi chuyển sang năm khác thì doanh thu sẽ được cộng dồn lại từ đầu.

Tạo biểu đồ thể hiện doanh thu lũy kế cho từng năm:

• Slicer để lọc ra các năm.
• Biểu đồ cột: X-Tháng, Y- Measure doanh thu lũy kế.

Ví dụ 2: Tính doanh thu lũy kế theo từng năm, tạo biểu đồ doanh thu lũy kế theo từng năm, (ngày kết thúc năm tài chính là 30/06).

Công thức DAX:

Doanh thu lũy kế kỳ 30/6 = TOTALYTD(SUM(Sales[Total Amount]),'Date'[Date],"30/6")

Chúng ta có thêm tham số “30/06” (có thể viết “06-30”,”30-06” thì DAX vẫn nhận) để xác định ngày kết thúc kỳ tính toán, năm tài chính là ngày 30/6.

Chúng ta có thể thấy rằng Measure vừa tạo sẽ tính doanh thu lũy kế từ tháng 7 năm trước đến tháng 6 năm sau thay vì để mặc định như ví dụ 1.

• TOTALQTD():

Ví dụ: Tính doanh thu lũy kế theo từng quý và tạo biểu đồ cột thể hiện doanh thu lũy kế theo từng quý.

CÔNG THỨC DAX:

Doanh thu = SUM(Sales[Total Amount])

Doanh thu lũy kế quý = TOTALQTD(SUM(Sales[Total Amount]),'Date'[Date])

Maxtrix dưới bao gốm:

• Cột Date.
• Doanh thu.
• Doanh thu lũy kế quý.

Như ta thấy trên bảng trên thì doanh thu đã được tính lũy kế theo từng tháng trong quý. Hết một quý, Power BI sẽ lặp lại cách tính từ đầu.

Ta có thể tạo thêm biểu đồ cột để theo dõi doanh thu lũy kế hàng quý:

• Slicer năm và quý.
• Barchar: cột X-thời gian, Y-Measure daonh thu lũy kế quý vừa tạo.

• TOTALMTD():

Ví dụ: Tính doanh thu lũy kế từ đầu tháng và tạo biểu đồ doanh thu lũy kế theo ngày.

CÔNG THỨC DAX:

Doanh thu = SUM(Sales[Total Amount])

Doanh thu lũy kế tháng = TOTALMTD(SUM(Sales[Total Amount]),'Date'[Date])

Maxtrix dưới bao gồm:

• Cột Date.
• Doanh thu.
• Doanh thu lũy kế tháng.

Như ta có thể thấy doanh thu tháng 12 đã được tính cộng dồn từ ngày đầu tháng, ngoài ra ta có thể trực quan hóa dữ liệu bằng biểu đồ gồm:

• Slicer năm, quý, tháng.
• Barchart: Cột X- Thời gian, Cột Y: Measure doanh thu lũy kế tháng vừa tạo.

CÁCH XÂY DỰNG HÀM TOTALWTD

Hiện tại POWER BI chưa cung cấp hàm DAX có tính năng tính doanh thu lũy kế từ đầu tuần nên chúng ta sẽ tiến hành viết một DAX thay thế cho hàm TOTALWTD, chúng ta vẫn dùng bộ dataset trên.

Lưu ý: Bảng thời gian cần có thêm cột ngày bắt đầu tuần, tham khảo cách tạo sau:

• Có thể tham khảo bài viết cách tạo bảng date.
• Tạo cột mới trong bảng DATE dựa trên công thức:

Start_of_week = 'Date'[Date] - WEEKDAY('Date'[Date],2) + 1

Với ‘Date'[Date] là cột Date.

CÔNG THỨC DAX CHO DOANH THU LŨY KẾ TUẦN:

TOTALWTD =

CALCULATE(

    SUM(Sales[Total Amount]),

    'Date'[Date]<=MAX('Date'[Date]),

    'Date'[Date]>=MAX('Date'[Start_of_Week]))

Trong đó:

• SUM(Sales[Total Amount]): Tính tổng doanh thu.
• FILTER:

1. ‘Date'[Date]<=MAX(‘Date'[Date] : Tính được doanh thu lũy kế theo thời gian.
2. ‘Date'[Date]>=MAX(‘Date'[Start_of_Week]: Tính doanh thu lũy kế nhưng chỉ tính với những giá trị bắt đầu tuần mới.

Lưu ý:

• Cách hoạt động hàm CALCULATE.
• Hàm doanh thu lũy kế tuần sẽ chỉ có ý nghĩa khi chúng ta quan sát theo ngày.

Kết quả:

File Power BI Demo

TẠM KẾT

Qua bài viết về hàm DAX cơ bản: nhóm hàm TOTAL TO DATE, Datapot hi vọng bạn có một cái nhìn rõ hơn về các hàm TOTALYTD, TOTALQTD, TOTALMTD. Ngoài ra còn có phần nhóm hàm DATES TO DATE cũng có chức năng tương tự nhóm hàm này. Để không bỏ lỡ các nội dung mới về tự học và thực hành Power BI, mời bạn theo dõi chuyên mục Power BI trên Blog và subscribe vào kênh YouTube của Datapot nhé, dưới đây là phần POWER BI để các bạn có cái nhìn tổng quan nhất về nhóm hàm TO DATE.

Ngô Hoàng Ngọc