Let’s begin with a easy desk:
Every row within the matrix visible exhibits the whole on-line gross sales for every month.
To this point, so good.
The interpretation is that we see the whole on-line gross sales filtered by month.
However this isn’t the whole fact.
Let’s check out the information mannequin:
While you look rigorously, you see that the connection is constructed between the 2 date columns.
There isn’t any relationship to the month column.
Once we take this route, the interpretation above isn’t completely correct.
The whole interpretation needs to be: Every row exhibits the whole of on-line gross sales filtered by the date desk. The rows of the Date desk are grouped by the months. Every row exhibits the whole gross sales for all days for every month.
We’re a step nearer to understanding DAX basically, and time intelligence capabilities specifically, after we notice this element.
Let’s go one step additional.
YTD and the Fundamental question
Now, let’s add a YTD Measure to look at what occurs:
The Measure is nothing particular, and the outcomes are simple to grasp.
Now, let’s check out what exactly the DATESYTD() operate does.
The reason from dax.guide says: “Returns a set of dates within the 12 months as much as the final date seen within the filter context”.
What does this imply precisely?
To dive into this query, let’s write a DAX question first, to get the listing of dates in June 2024, as is completed within the visualization above:
DEFINE
VAR YearFilter = TREATAS({ 2024 }, 'Date'[Year])
VAR MonthFilter = TREATAS({ 6 }, 'Date'[Month])
EVALUATE
SUMMARIZECOLUMNS('Date'[Date]
,YearFilter
,MonthFilter
)The result’s an inventory of 30 days for June:
That is the filter utilized to the row for June 2024 within the Matrix proven above.
What’s the end result after we apply the DATESYTD() operate to the end result?
Right here is the question:
DEFINE
VAR YearFilter = TREATAS({ 2024 }, 'Date'[Year])
VAR MonthFilter = TREATAS({ 6 }, 'Date'[Month])
VAR BasisDates = CALCULATETABLE(
SUMMARIZECOLUMNS('Date'[Date]
,YearFilter
,MonthFilter
)
)
VAR YTDDates = DATESYTD(TREATAS(BasisDates, 'Date'[Date])
)
EVALUATE
YTDDatesAnd right here, the end result:
It’s an inventory of 182 rows, containing all dates ranging from the start of the 12 months as much as the final day of June 2024.
That is the definition of YTD.
Once we take a look at the next Measure:
On-line Gross sales (YTD) =
VAR YTDDates = DATESYTD('Date'[Date])
RETURN
CALCULATE([Sum Online Sales]
,YTDDates
)We notice that the variable YTDDates is “solely” an inventory of dates utilized as a filter to the CALCULATE() operate.
That is the important thing to all Time intelligence capabilities.
Return one 12 months – some examples
What occurs when making use of one other operate to the end result?
For instance, SAMEPERIODLASTYEAR()?
To reply this query, I exploit the next DAX question:
DEFINE
VAR YearFilter = TREATAS({ 2024 }, 'Date'[Year])
VAR MonthFilter = TREATAS({ 6 }, 'Date'[Month])
VAR BasisDates = CALCULATETABLE(
SUMMARIZECOLUMNS('Date'[Date]
,YearFilter
,MonthFilter
)
)
VAR YTDDates = DATESYTD(TREATAS(BasisDates, 'Date'[Date])
)
VAR YTDDatesPY = SAMEPERIODLASTYEAR(YTDDates)
EVALUATE
YTDDatesPYI deliberately separated the calling of SAMEPERIODLASTYEAR() from DATESYTD() to make it simpler to learn. It could have been potential to nest DATESYTD() into SAMEPERIODLASTYEAR().
This time, we’ve 181 rows, as 2024 was a bissextile year.
And the dates are shifted again by one 12 months:
So, once more, after we apply a Time Intelligence operate to a measure, the operate, for instance, DATESYTD(), returns an inventory of dates.
Please observe: When making use of the filter, any present filters on the date desk are eliminated.
Customized logic
Now, let’s use this data on customized time intelligence logic.
First, let’s barely change the filter for the 12 months and month:
DEFINE
VAR YearMonthFilter = TREATAS({ 202406 }, 'Date'[MonthKey])
EVALUATE
SUMMARIZECOLUMNS('Date'[Date]
, YearMonthFilter
)The results of this question is identical as firstly of this text.
This time, I set the filter with a numerical worth on the [MonthKey] column.
How can I am going again to the earlier 12 months?
Should you assume mathematically, it’s simply by subtracting 100:
202406 – 100 = 202306
Let’s attempt it:
You may also do that with different numeric codecs.
While you take a fiscal 12 months, for instance, like this: 2425 (For the Fiscal 12 months 24/25)
You may deduce 101 to get the earlier fiscal 12 months: 2425 – 101 = 2324
One other instance of a customized time intelligence logic is a working common, the place for every day, we calculate the common worth over the previous 10 days:
Because the content material of the variable DateRange is once more an inventory of dates, I can apply the SAMEPERIODLASTYEAR() operate to it, and get the end result I want:
DEFINE
VAR YearFilter = TREATAS({ 2024 }, 'Date'[Year])
VAR MonthFilter = TREATAS({ 6 }, 'Date'[Month])
// 1. Get the primary and final Date for the present Filter Context
VAR MaxDate = CALCULATE(MAX( 'Date'[Date] )
,YearFilter
,MonthFilter
)
VAR MinDate =
CALCULATE(
DATEADD( 'Date'[Date], - 10, DAY )
,'Date'[Date] = MaxDate
)
// 2. Generate the Date vary wanted for the Transferring common (4 months)
VAR DateRange =
CALCULATETABLE(
DATESBETWEEN( 'Date'[Date]
,MinDate
,MaxDate
)
)
EVALUATE
SAMEPERIODLASTYEAR( DateRange )And that is the end result:
This logic returns 11 rows, because it contains the final day of the month. Relying on the required end result, we have to regulate the best way we calculate the primary and final dates of the date listing (the filter utilized to the measure).
After all, it is a repetition of what I confirmed above. Nonetheless, it demonstrates that the identical strategy may be utilized to varied eventualities.
As quickly as you perceive this, your work with time intelligence capabilities and different capabilities that settle for tables of values as enter will change into a lot simpler to understand and grasp.
Conclusion
Whereas I used DAX Studio for the queries, you should use the identical queries within the DAX Question software inside Energy BI Desktop.
I deliberately used these queries to exhibit that we work with tables on a regular basis in DAX, though we might not at all times pay attention to it.
However it’s an vital element that helps us on our approach to understanding DAX.
Though among the DAX code proven right here could also be out of date with the appearance of the brand new calendar-based time intelligence function in Energy BI, the rules defined right here stay legitimate. The capabilities, akin to DATESYTD() or SAMEPERIODLASTYEAR(), nonetheless exist and work in the identical approach as earlier than. In the intervening time, nothing will change on this aspect, because the ideas described listed here are nonetheless legitimate.
References
Like in my earlier articles, I exploit the Contoso pattern dataset. You may obtain the ContosoRetailDW Dataset at no cost from Microsoft here.
The Contoso Knowledge may be freely used underneath the MIT License, as described in this document. I modified the dataset to shift the information to modern dates.
