Hi vọng ở bài viết trước, mọi người không thấy quá phức tạp và hiểu được logic đằng sau các phép tính thời gian của DAX sử dụng CALCULATE(), FILTER(), ALL(). Và bây giờ, không để mọi người chờ lâu, KPIM sẽ bàn đến các công thức hàm DATESYTD(). Đây chính là công cụ vô cùng hữu dụng giúp mọi người sử dụng những hàm ngắn gọn và đơn giản thay thế cho các công thức phức tạp và dài dòng lúc trước.
Time Intelligence, tạm dịch là các phép tính thời gian thông mình, là một chuỗi các hàm tính được thiết lập sẵn để trả về các khoảng thời gian nhất định mà mọi người hay sử dụng.
Ví dụ như YTD hay Year-to-date là khoảng thời gian từ đầu năm đến hiện tại và tương tự là Month-to-date (MTD) và Quarter-to-date (QTD). Hoặc cũng có thể trả về luôn tổng giá trị dựa trên các phép tính trong khoảng thời gian đó.
Hàm DATESYTD()
Một trong những yêu cầu nghiệp vụ khá phổ biến là tính doanh thu hoặc các KPIs trong khoảng thời gian từ đầu năm cho đến hiện tại và so sánh nó với cùng kỳ năm ngoái.
Để làm được phép tính này, chúng ta đã biết từ bài viết trước dựa trên hàm tính lọc khoảng thời gian trong bảng DATE sao cho bao gồm những ngày cùng năm và trước ngày hiện tại. Bằng DATESYTD, chúng ta sẽ không cần phải phức tạp hóa vấn đề như vậy nữa, thay bằng cả cụm công thức sau Filter(), chúng ta chỉ đơn giản dùng () dưới dạng dưới đây:
DATESYTD(<dates>[,<year_end_date>])
DATESYTD sẽ nhìn vào trường Date (trong trường hợp của chúng ta chính là trường Date trong Date Dimension) và sau đó có thể bổ sung tháng cuối năm nếu có. Còn nếu để không thì DAX sẽ tự hiểu năm kinh doanh dừng vào tháng 12 như bình thường.
| Trường công thức | Ý nghĩa |
|---|---|
DATESYTD('Date'[Date]) |
Mặc định tháng cuối năm là tháng 12 |
DATESYTD('Date'[Date], "06/30") |
Tháng cuối năm là tháng 6 |
Một điểm quan trọng cần để ý đó là DATESYTD() trả về một dãy các ngày trong khoảng thời gian được xác định bởi ngày trong Filter Context (tức là ngày trong từng dòng ở bảng sử dụng measure này). Ví dụ:
DATESYTD() trả về một dãy các ngày trong khoảng thời gian được xác định
Ở bảng trên, với mỗi dòng, chúng ta sẽ có filter context là ngày tại dòng đó. Lấy ví dụ ở dòng cuối cùng ngày 14/1/2013, DATESYTD sẽ xác định các bước sau:
- Tìm năm của ngày đó -> 2013
- Xác định ngày đầu tiên trong năm đó -> 1/1/2013
- Trả về dãy các ngày từ ngày đầu năm đến ngày đó: 1/1/2013 – 14/1/2013
Từ đây chúng ta có thể dễ dàng thấy công thức này chính là công thức rút gọn của
FILTER(ALL('Date'[Date]),
YEAR('Date'[Date]) = YEAR(MAX('Date'[Date])) &&
'Date'[Date] <= MAX('Date'[Date])
))
Những người anh em DATESMTD() và DATESQTD()
Tương tự như DATESYTD() thì DATESMTD() và DATESQTD() được sử dụng tương tự để trả về khoảng thời gian từ đầu tháng cho đến hiện tại (MTD) và từ đầu kỳ đến hiện tại (QTD). Điểm khác biệt duy nhất đó là ở 2 công thức này, bạn không cần bổ sung điều kiện tháng cuối năm nữa.
Tính lũy kế năm sử dụng DATESYTD()
Để tính lũy kế năm, chúng ta chỉ đơn giản bổ sung thêm công thức doanh thu ở trước và đặt DATESYTD() vào phần date của công thức CALCULATE. Giờ chúng ta đã có một công thức đơn giản ngắn gọn hơn rất nhiều:
Total Sales YTD with Time Intelligence =
CALCULATE([Total Sales], DATESYTD('Date'[Date]))
Tính lũy kế năm sử dụng DATESYTD()
Trong ví dụ trên, ta có thể thấy dễ dàng công thức mới dùng Time Intelligence sẽ trả về giá trị tương tự như Total Sales YTD bằng cách tính cơ bản trước kia. Hãy thử áp dụng với năm kế toán dừng ở tháng 6:
Total Sales YTD ending June =
CALCULATE([Total Sales], <strong>DATESYTD</strong>('Date'[Date],"06/30"))
Chúng ta sẽ thấy khi áp dụng thêm điều kiện tháng cuối năm, DATESYTD() sẽ xác định ngày đầu năm mới là 1/7/2013. Từ đó, giá trị lũy kế năm sẽ được tính lại từ đầu từ 1/7/2013.
Tính lũy kế năm sử dụng TOTALYTD(), anh họ của DATESYTD()
Chỉ nhìn thôi chắc các bạn cũng đoán ra, đây là một cách rút ngắn gọn nữa của công thức tính lũy kế năm. Thay vì sử dụng CALCULATE() và DATESYTD() để tính, chúng ta sẽ gộp cả hai lại chỉ bằng hàm TOTALYTD(). Có thể nói đây là hàm được ứng dụng nhiều nhất và mình thấy hữu ích nhất trong gia đình “-to-date”.
Ví dụ, thay vì chúng ta có:
Total Sales YTD ending June =
CALCULATE([Total Sales], DATESYTD('Date'[Date],"06/30"))
Chúng ta sẽ sử dụng hàm sau:
TOTALYTD = TOTALYTD([Total Sales],'Date'[Date])
Bên cạnh cách tính đơn giản như trên, bạn cũng sẽ có thêm 2 lựa chọn để thay đổi theo ý muốn. Thứ nhất, chúng ta có thể bổ sung thêm các Filter nếu cần thiết (ví dụ tính lũy kế năm của duy nhất 1 loại sản phẩm), và đương nhiên là không thể thiếu lựa chọn bổ sung tháng cuối năm rồi!
Tính lũy kế năm sử dụng TOTALYTD()
Và kết quả là với công thức rút gọn ngắn nhất chúng ta có được cùng kết quả như mong đợi:
Và cũng tương tự, tên TOTALYTD() này cũng có mấy ông anh em các bạn ạ. TOTALMTD() và TOTALQTD() được áp dụng tương tự để tính cho MTD và QTD nha.
Kết luận
Vậy là trong bài viết này, KPIM đã đề cập cho các bạn cả họ hàng nhà YTD rồi nhé. Giờ đây, thay vì sử dụng công thức bình thường dài và phức tạp, chúng ta có thể lựa chọn dùng DATESYTD() hoặc TOTALYTD() để tính lũy kế năm. Chúng ta cũng có thể dung DATESMTD() hoặc TOTALMTD() để tính lũy kế tháng.
Hi vọng rằng qua bài viết này, KPIM đã giúp các bạn sẽ hiểu thêm về Time Intelligence và các cách tính lũy kế để có được giải pháp tối ưu cho báo cáo của mình nhé. Hãy đón chờ các bài viết tiếp theo về Time Intelligence để trang bị những hàm DAX hữu ích cho báo cáo và các nhu cầu nghiệp vụ nhé! Happy analyzing!
