Sep 5, 2020
Power BI — Time Intelligence
Today’s post is about time intelligence DAX formulas. I want to show you a few examples.
At first I imported a [Users] table from the Stack Overflow database and a [Date] table.
I created relationships between tables and marked the [Date] table as a date table type.
The first example, we want to show an account creation cumulative quantity by year, quarter, month.
But, what we should do when we want to use the cumulative total from another point in time. Let’s assume that we want to start from the half of the year. We can use the DATESYTD() function with an optional parameter [TearEndDate] or the TOTALYTD() function which had been used earlier.
As you can see, our calculation window starts in July and ends in June.
Th next example.
- I want to compare the current value to last year and the same period quantity.
- Compare the current value to the last month value.
We can use two measures to reach the goal.
What if we want to compute a moving sum, let’s say for the last two months?
At the end, let’s check how much new accounts quantity changed year to year.
% Change = DIVIDE([Quantity] - [Quantity Last Year Period],[Quantity Last Year Period])
