Sau khi tạo Custom Date Dimension và mark chúng thành Date table, liên kết với dữ liệu bán hàng qua OrderDate(). Chúng ta giờ có thể sử dụng Custom Date Dimension để dễ dàng tính các công thức theo thời gian. Trong bài viết này, mình sẽ tập trung nói về các công thức đơn giản dùng CALCULATE() và FILTER(), ALL() để tính các giá trị mà không sử dụng Time Intelligence.
Chắc các bạn đang nghĩ tại sao không dung Time Intelligence ngay mà phải tính toán lằng nhằng phức tạp. Lí do là việc sử dụng các công thức cơ bản này sẽ giúp bạn hiểu rõ cách thức hoạt động của Custom Date Dimension cùng DAX, để từ đó hiểu rõ được các công thức trong Time Intelligence hoạt động ra sao.
Cách thức hoạt động giữa Custom Date Dimension và bảng dữ liệu bán hàng
Bằng việc tạo mối liên hệ với dữ liệu bán hàng qua OrderDate(), chúng ta có thể sử dụng trường Date để thay thế OrderDate() trong các công thức. Điều này giúp measures của bạn trở nên dễ viết, dễ quản lí và chính xác hơn.
Cách thức hoạt động giữa Date Dimension
Với mối liên hệ này, các công thức tính trên bảng SalesOrder khi nhìn theo trường thời gian OrderDate sẽ tương tự như sử dụng trên trường Date của Date Dimension. Việc này sẽ vô cùng tiện lợi vì chúng ta có một bảng Date được format đầy đủ thay vì phải format lại trường OrderDate.
Ví dụ như, chúng ta có thể tạo measure tính tổng giá trị bán hàng và đặt trong bảng Date để xem số liệu bán hàng theo từng ngày hoặc tháng.
Total Sales = CALCULATE(SUM('Sales SalesOrderHeader'[TotalDue]))
Bảng dữ liệu SalesOrder
Tính giá trị của tháng, năm nhất định bằng CALCULATE(), FILTER()
Để tính giá trị bán hàng từng ngày, tháng và năm, chúng ta có thể đơn giản dùng measure Sales như cách trên. Tuy nhiên, trong nhiều trường hợp, chúng ta muốn tính giá trị không đổi của một tháng, hoặc năm nào đó.
Ví dụ, chúng ta có giá trị bán hàng của từng tháng trong năm 2013 và muốn so sánh với tổng doanh thu của cả năm cũng như tính % bán hàng tăng dần so với năm đó (ví dụ dưới đây).
Để tạo được chart như trên, bên cạnh doanh thu tháng Total Sales đã có, chúng ta cần tạo một measure trả về giá trị không đổi của doanh thu năm 2013 và từ đó tính được % so với tổng cả năm. Vậy phải làm sao để tạo measure này? Thử ném điều kiện Year = 2013 xem nào, chắc sẽ giải quyết được vấn đề thôi nhỉ.
Total Sales 2013 =
CALCULATE(SUM('Sales SalesOrderHeader'[TotalDue]),YEAR('Date'[Date])=2013)
Quả nhiên không ngoài dự đoán, bằng cách đặt điều kiện năm, chúng ta có thể tính được tổng doanh thu cả năm 2013 tuy nhiên thì measure được sử dụng như trên sẽ có hạn chế về tính linh hoạt. Vì chúng ta đang dùng giá trị không đổi 2013 nên mỗi lần muốn tính sang các năm kế tiếp, ta lại phải thay đổi giá trị này.
Sau đây là một phương pháp tiện lợi hơn để giúp tính giá trị năm một cách linh hoạt dựa trên Row Context của Date. Phương pháp sử dụng MAX(DATE) sẽ luôn giúp bạn tính các giá trị dựa trên trường thời gian Date và linh hoạt hơn rất nhiều.
Total Sales by year =
CALCULATE(SUM('Sales SalesOrderHeader'[TotalDue]),
FILTER(ALL('Date'),YEAR('Date'[Date]) = YEAR(MAX('Date'[Date]))))
Các bạn có thể thấy đây là một công thức tương tự như công thức ban đầu nhưng thay vì sử dụng giá trị không đổi là năm 2013, chúng ta sẽ đẩy nó về giá trị biến đổi theo trường DATE đó là trả về năm của ngày Date tại dòng đó (MAX(DATE). Các bạn để ý trong từng dòng, giá trị MAX(DATE) sẽ được trả về bằng giá trị DATE của dòng đó.
Bên cạnh đó, vì chúng ta không sử dụng một giá trị mặc định như 2013, chúng ta sẻ phải bổ sung trong công thức Filter để định dạng điều kiện lọc có giá trị biến đổi. Từ đó, công thức của chúng ta không chỉ tính mỗi năm 2013 mà tính các năm dựa theo trường Date như dưới đây:
Tính các năm dựa theo trường Date
Có thể dễ dàng thấy các dòng ngày 2013 sẽ trả về tổng doanh thu của 2013 và các dòng ngày 2014 sẽ trả về tổng năm 2014. Các bạn cũng chú ý ở dòng tổng Total, công thức sẽ nhìn trên toàn bộ bảng dữ liệu với MAX(DATE) là vào năm 2014, vì vậy total của chúng ta sẽ là tổng doanh thu của 2014.
Từ đó, chúng ta có measure linh hoạt theo thời gian và chỉ việc filter Date để thay đổi các khoảng thời gian muốn nhìn đến thay vì chỉnh trong công thức.
Tính giá trị lũy kế năm sử dụng ALL()
Để tính giá trị % lũy kế so với tổng doanh thu cả năm, chúng ta phải tính tổng doanh thu lũy kế theo năm trước. Giá trị lũy kế theo năm hiểu đơn giản là tổng doanh thu trong 1 năm được cộng gộp theo từng ngày.
Để dịch ra theo hàm ý công thức DAX, hãy nhìn vào từng dòng, chúng ta cần tính Total Sales với các ngày trong cùng năm và chỉ bao gồm những ngày trước ngày trong dòng đó. Chúng ta sẽ áp dụng công thức với ALL() cùng MAX() như sau để tính giá trị lũy kế năm:
Total Sales YTD =
CALCULATE([Total Sales], FILTER(ALL('Date'[Date]),
YEAR('Date'[Date]) = YEAR(MAX('Date'[Date])) && 'Date'[Date] <= MAX('Date'[Date])
))
Các bạn để ý khi sử dụng FILTER, chúng ta chỉ có thể đưa vào một điều kiện lọc, vì vậy để bổ sung điều kiện lọc thứ 2, chúng ta sẽ sử dụng dấu &&.
Khi sử dụng && sẽ tương tự như dùng AND tức là hai điệu kiện đều phải được đáp ứng. Với dạng logic OR, tức là một trong 2 điều kiện đáp ứng là đủ, ta sẽ sử dụng dấu ||.
FILTER(<table>,<filter>)
Bằng công thức này, chúng ta sẽ có giá trị lũy kế tĩnh theo từng ngày cho đến hết năm và tính lại từ đâu cho năm kế tiếp.
Để tính % thay đổi dựa trên giá trị từng tháng so với cả năm, chúng ta sẽ dùng công thức DIVIDE như sau:
Monthly Sales % of Year = DIVIDE([Total Sales YTD], [Total Sales by year],0)
Sau đó chúng ta sẽ dùng Combo Chart – Line and Bar để miêu tả doanh thu hàng tháng và giá trị lũy kế theo % so với doanh thu cả năm như sau:
Kết luận
Từ những ví dụ trên, hi vọng các bạn có thể sử dụng các công thức bằng CALCULATE(), FILTER(), ALL(), MAX() để dễ dàng tính các giá trị theo thời gian, lũy kế một cách cơ bản. Đây sẽ là một cách tốt để hiểu DAX hoạt động như thế nào. Từ đó trong những bài viết sau đề cập về các công thức thời gian thông minh (Time Intelligence), mình sẽ liên hệ lại về những công thức này đề mọi người hiểu rõ hơn và từ đó sử dụng một cách hợp lý.
Xin chào và nhớ đón chờ các bài viết tiếp theo về Custom Date Dimension Time Intelligence để không còn vướng bận trong cách sử dụng nha! Happy Analyzing!
