Implementing time intelligence using DAX
Most reports have a time quality; we often need to compare now versus some previous point in time to see whether we are trending in the right direction. Power BI offers many powerful time intelligence functions to handle this kind of reporting.
In Chapter 5, Designing a Data Model, we covered how to create a date table. This chapter explains how to use its awesomeness.
Time intelligence functions fall into one of two types:
- Functions that require
CALCULATE. - Functions that return a scalar value on their own. This is sometimes referred to as syntactic sugar, which is an awesome name.
A function that uses the DATESYTD call requires CALCULATE:
Products Ordered YTD = CALCULATE(COUNT(Sales[Product SKU]),DATESYTD('Date'[Date]))
Syntactic sugar has an aggregate value in its name, such as TOTALYTD or CLOSINGBALANCEMONTH. We will get the same results as the CALCULATE measure using the following:
Total Products...
