Let’s begin with a easy desk:
Every row within the matrix visible reveals the whole on-line gross sales for every month.
To date, so good.
The interpretation is that we see the whole on-line gross sales filtered by month.
However this isn’t your entire reality.
Let’s check out the information mannequin:
Once you look rigorously, you see that the connection is constructed between the 2 date columns.
There isn’t a relationship to the month column.
Once we take this route, the interpretation above just isn’t totally correct.
The whole interpretation must be: Every row reveals 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 reveals the whole gross sales for all days for every month.
We’re a step nearer to understanding DAX normally, and time intelligence features particularly, once 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 straightforward to grasp.
Now, let’s check out what exactly the DATESYTD() perform 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 finished 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 outcome once we apply the DATESYTD() perform to the outcome?
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 outcome:
It’s a listing 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” a listing of dates utilized as a filter to the CALCULATE() perform.
That is the important thing to all Time intelligence features.
Return one 12 months – some examples
What occurs when making use of one other perform to the outcome?
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’ve got 181 rows, as 2024 was a bissextile year.
And the dates are shifted again by one 12 months:
So, once more, once we apply a Time Intelligence perform to a measure, the perform, for instance, DATESYTD(), returns a listing of dates.
Please notice: 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 12 months and month:
DEFINE
VAR YearMonthFilter = TREATAS({ 202406 }, 'Date'[MonthKey])
EVALUATE
SUMMARIZECOLUMNS('Date'[Date]
, YearMonthFilter
)The results of this question is similar as initially 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 12 months?
In the event you suppose mathematically, it’s simply by subtracting 100:
202406 – 100 = 202306
Let’s strive it:
It’s also possible to do that with different numeric codecs.
Once you take a fiscal 12 months, for instance, like this: 2425 (For the Fiscal Yr 24/25)
You’ll be able to deduce 101 to get the earlier fiscal 12 months: 2425 – 101 = 2324
One other instance of a customized time intelligence logic is a operating common, the place for every day, we calculate the typical 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 outcome 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 outcome:
This logic returns 11 rows, because it consists of the final day of the month. Relying on the required outcome, 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. Nevertheless, it demonstrates that the identical strategy will be utilized to varied situations.
As quickly as you perceive this, your work with time intelligence features and different features that settle for tables of values as enter will turn out to be a lot simpler to understand 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 reveal that we work with tables on a regular basis in DAX, though we might not all the time concentrate on it.
However it’s an necessary element that helps us on our technique to understanding DAX.
Though a number of 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, equivalent to DATESYTD() or SAMEPERIODLASTYEAR(), nonetheless exist and work in the identical means 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 at no cost from Microsoft here.
The Contoso Knowledge will be freely used underneath the MIT License, as described in this document. I modified the dataset to shift the information to modern dates.
