We’re going to dive into some custom Power BI calendar tables in this tutorial. Many organizations don’t work by the standard calendar date – they might work by a financial week number or by a custom week number. You may watch the full video of this tutorial at the bottom of this blog.
However, the time intelligence calculations in Power BI just don’t work with these custom or non-standard calendars. In this tutorial, I’ll show you what I mean by this. In this demonstration, we’ll calculate the year-to -date (YTD) number based on the week, specifically for custom Power BI calendar tables using some DAX techniques.
Why Time Intelligence Calculations Don’t Work On Custom Calendars
In this table, we have information from 2013. We have the Financial Month Number, Financial Week Number, Week of Quarter, and Calendar Week Number.
However, if we come to the end of the year, the Calendar Week Number goes over the end of the year; so, we have two days inside of 2013 that are in week 1, and the rest are in 2014.
This is where we can run into trouble because the time intelligence calculations work directly over a calendar date. We can’t utilize any of the good time intelligence functions, as none of them will work. So we have to create a custom logic, usually by using the FILTER function to work out these numbers.
But before we jump into the YTD number for custom calendars, let’s have a quick look on how it’s calculated for a standard calendar.
A Standard Date Calendar Calculation
In this example, we assume that our financial year started in May and we wanted to run a cumulative total for that financial year. This is how it’s done on the standard date calendar.
All we have to do here is put in the Date column, and then we type in 30/4, which signifies that it’s the last day in the financial year. Once we apply that to the date context, we get the correct result. It just accumulates through time, and then rounds off at the end of April 2014 as the total for that financial year.
It becomes tricky when we bring this calculation into this table on the right, which only has the Financial Year and the Financial Week numbers. It calculates correctly, but we run into an issue because the date column does not overlay perfectly over the Financial Week number.
Anything in 2015 would be wrong and so we can’t use this calculation for custom calendars.
Calculating YTD Sales For Non-Standard Calendars
So, let’s create a new measure and call it Total Sales YTD – Custom. We’ll use variables (VAR) to make things easier and more intuitive. Our variables are CurrentFinWeek and CurrentFinYear – here we use SELECTEDVALUE.
The second part of the calculation is where we write the logic. We’re still calculating Total Sales, but in a different context to what the normal time intelligence calculations do. That’s why we use FILTER, which solves most of these custom calendars. We also use the ALL function, and then go Calendar Daily, which releases any context on the calendar. After that, we’re going to re-apply the context based on the logic here.
The ‘Calendar Daily’ [Fin Week Number] is going to iterate through every single Financial Week number, but we want to always have the current Financial week number in a particular row where that evaluation is happening.
Now, if we drag this in, we see that this calculates the correct result for us based on a custom calendar.
***** Related Links*****
Time Comparison For Non Standard Date Tables In Power BI
Calculating Weekly Sales w/DAX In Power BI
Power BI Custom Calendars: Calculating For Month On Month Change – 445 Calendars
Conclusion
This tutorial highlights an effective technique that you can use when working with a Power BI calendar, specifically one that does not follow the standard date calendar. You cannot use the time intelligence formulas with these non-standard calendar tables, which is why I’ve created the logic to solve this.
I hope you find this blog useful when dealing with custom calendars. Check out the video and links below for more details on this tutorial and other related resources.
All the best!
Sam
[youtube https://www.youtube.com/watch?v=aODroF37pq0?rel=0&w=784&h=441]
