Download PBIX File with DAX formulas
DAX has a lot of nice functions to make Time Intelligence with Months and Quarters easy. But what if your company uses its own Calendar with custom months and quarters like in the 4-4-5 or 5-4-4 system (4-4-5 Calendar Wikipedia)? Then things become a little bit more challenging. In this article I will show you exactly how to tackle this problem.
As an example I will work with the 5-4-4 system but the formula’s can easily be translated to work with the 4-4-5 system or any other system that your company uses. It will even work with weeks.
5-4-4 Date Table
So let’s first start by defining the 5-4-4 Months and Quarters in our Date Table as Calculated Columns:
544 Month =
SWITCH (
TRUE ();
[Week] <= 5; 1; –5
[Week] <= 9; 2; –4
[Week] <= 13; 3; –4
[Week] <= 18; 4; –5
[Week] <= 22; 5; –4
[Week] <= 26; 6; –4
[Week] <= 31; 7; –5
[Week] <= 35; 8; –4
[Week] <= 39; 9; –4
[Week] <= 44; 10; –5
[Week] <= 48; 11; –4
12
)
544 Quarter Nr =
SWITCH (
TRUE ();
[Week] <= 13; 1; — Q1
[Week] <= 26; 2; — Q2
[Week] <= 39; 3; — Q3
4
)
Revenue same 5-4-4 Quarter previous Year
Now let’s dive into the DAX Time Intelligence formulas. First I want to create a measure to compare the Revenue of a [554 Quarter] with the same period in the previous year, like in the chart below:
The Revenue is defined as:
Revenue =
SUM ( Orders[LineTotal] )
To get the Revenue of the same Quarter in the previous Year we use our Date table to lookup what the start and end date of this period is:
Revenue Same 544 Quarter Previous Year =
VAR currentQuarter = MIN ( Dates[544 Quarter Nr] ) — this returns the current Quarter
VAR currentYear = MIN ( Dates[Year] ) — this returns the current Year
VAR prevYear = currentYear – 1 — substract 1 to get the previous Year
VAR startDate =
CALCULATE (
MIN ( Dates[Date] ); — calculate the first date in the same quarter of the previous year
ALL ( Dates ); — clear all filters from the Date table to break free of the current quarter
Dates[Year] = prevYear;
Dates[544 Quarter Nr] = currentQuarter
)
VAR endDate =
CALCULATE (
MAX ( Dates[Date] ); — calculate the last date in the same quarter of the previous year
ALL ( Dates ); — clear all filters from the Date table to break free of the current quarter
Dates[Year] = prevYear;
Dates[544 Quarter Nr] = currentQuarter
)
— calculate the Revenue between the start and end date of the same Quarter in the previous Year:
VAR result = CALCULATE ( [Revenue]; DATESBETWEEN ( Dates[Date]; startDate; endDate ) )
RETURN result
YTD Revenue with 5-4-4 Quarters
An other common requested Time Intelligence formula is calculating the YTD and compare it with the same period of the previous Year:
To get the Revenue YTD we calculate the SUM of the Revenue from the first of the current Year up to the end date of the current quarter:
Revenue YTD 544 Quarter=
VAR maxDate = MAX ( Dates[Date] ) — get the last date in the current quarter
VAR yearMaxDate = YEAR ( maxDate ) — take the year part of the date
VAR startDate = DATE ( yearMaxDate; 1; 1 ) — assemble a new date starting at the first of January
VAR endDate = maxDate
VAR result = CALCULATE ( [Revenue]; DATESBETWEEN ( Dates[Date]; startDate; endDate ) )
RETURN result
To get the Revenue YTD of the same period in the previous Year we use our Date table again to lookup what the start and end date of this period is:
Revenue YTD 544 Quarter Previous Year =
VAR currentQuarter = MIN ( Dates[544 Quarter Nr] )
VAR currentYear = MIN ( Dates[Year] )
VAR prevYear = currentYear – 1
VAR startDate = DATE ( prevYear; 1; 1 )
VAR endDate =
CALCULATE (
MAX ( Dates[Date] );
ALL ( Dates );
Dates[544 Quarter Nr] = currentQuarter;
Dates[Year] = prevYear
)
VAR result = CALCULATE ( [Revenue]; DATESBETWEEN ( Dates[Date]; startDate; endDate ) )
RETURN result
What do you think?
Which custom Calendar is you company using? What custom Time Intelligence would you like to do? Put your comments below.
Thanks but I would love to see it for the 4-4-5 calendar though because that’s what we use.