Let’s begin with a easy desk:
Every row within the matrix visible reveals the full on-line gross sales for every month.
Up to now, so good.
The interpretation is that we see the full on-line gross sales filtered by month.
However this isn’t your entire fact.
Let’s check out the information mannequin:
While you look fastidiously, you see that the connection is constructed between the 2 date columns.
There isn’t any relationship to the month column.
After we take this route, the interpretation above shouldn’t be fully correct.
The whole interpretation must be: Every row reveals the full of on-line gross sales filtered by the date desk. The rows of the Date desk are grouped by the months. Every row reveals the full gross sales for all days for every month.
We’re a step nearer to understanding DAX normally, and time intelligence features 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() perform does.
The reason from dax.information says: “Returns a set of dates within the yr 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 record 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 a listing of 30 days for June:
That is the filter utilized to the row for June 2024 within the Matrix proven above.
What’s the consequence after we apply the DATESYTD() perform to the consequence?
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 consequence:
It’s a listing of 182 rows, containing all dates ranging from the start of the yr as much as the final day of June 2024.
That is the definition of YTD.
After 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” a listing of dates utilized as a filter to the CALCULATE() perform.
That is the important thing to all Time intelligence features.
Return one yr – some examples
What occurs when making use of one other perform to the consequence?
For instance, SAMEPERIODLASTYEAR()?
To reply this query, I take advantage of 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 attainable to nest DATESYTD() into SAMEPERIODLASTYEAR().
This time, we now have 181 rows, as 2024 was a bissextile year.
And the dates are shifted again by one yr:
So, once more, after we apply a Time Intelligence perform to a measure, the perform, for instance, DATESYTD(), returns a listing of dates.
Please word: When making use of the filter, any present filters on the date desk are eliminated.
Customized logic
Now, let’s use this information on customized time intelligence logic.
First, let’s barely change the filter for the yr and month:
DEFINE
VAR YearMonthFilter = TREATAS({ 202406 }, 'Date'[MonthKey])
EVALUATE
SUMMARIZECOLUMNS('Date'[Date]
, YearMonthFilter
)The results of this question is similar as in the beginning of this text.
This time, I set the filter with a numerical worth on the [MonthKey] column.
How can I’m going again to the earlier yr?
Should you suppose mathematically, it’s simply by subtracting 100:
202406 – 100 = 202306
Let’s strive it:
You can too do that with different numeric codecs.
While you take a fiscal yr, for instance, like this: 2425 (For the Fiscal 12 months 24/25)
You’ll be able to deduce 101 to get the earlier fiscal yr: 2425 – 101 = 2324
One other instance of a customized time intelligence logic is a operating 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 a listing of dates, I can apply the SAMEPERIODLASTYEAR() perform to it, and get the consequence 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 Shifting common (4 months)
VAR DateRange =
CALCULATETABLE(
DATESBETWEEN( 'Date'[Date]
,MinDate
,MaxDate
)
)
EVALUATE
SAMEPERIODLASTYEAR( DateRange )And that is the consequence:
This logic returns 11 rows, because it consists of the final day of the month. Relying on the required consequence, we have to regulate the way in which we calculate the primary and final dates of the date record (the filter utilized to the measure).
After all, it is a repetition of what I confirmed above. Nonetheless, it demonstrates that the identical strategy could be utilized to numerous eventualities.
As quickly as you perceive this, your work with time intelligence features and different features that settle for tables of values as enter will change into a lot simpler to grasp and grasp.
Conclusion
Whereas I used DAX Studio for the queries, you should utilize the identical queries within the DAX Question instrument inside Energy BI Desktop.
I deliberately used these queries to display that we work with tables on a regular basis in DAX, though we could 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 arrival of the brand new calendar-based time intelligence characteristic in Energy BI, the ideas defined right here stay legitimate. The features, similar to DATESYTD() or SAMEPERIODLASTYEAR(), nonetheless exist and work in the identical manner as earlier than. In the interim, nothing will change on this aspect, because the ideas described listed below are nonetheless legitimate.
References
Like in my earlier articles, I take advantage of the Contoso pattern dataset. You’ll be able to obtain the ContosoRetailDW Dataset without spending a dime from Microsoft right here.
The Contoso Information could be freely used below the MIT License, as described on this doc. I modified the dataset to shift the information to up to date dates.
