In this post, I want to dive into how you can run analysis in Power BI with a non-standard calendar.
This is quite a common occurrence out there because not everyone works on the standard calendar and standard months, weeks, quarters, etc.
What I have decided to do is showcase how you can still run time comparisons and time-related analysis in Power BI using DAX formulas.
Unfortunately, this is not as easy as utilizing the standard time intelligence functions. These functions have been built and optimized to run over a standard calendar. They do not replicate results or calculations over a non-standard calendar. So we have to do something different. We have to write out, in full, the logic within a formula.
I will give you two examples here, the first is we will compare time periods and then the next is I will show you how you can create a cumulative or YTD total based on these non-standard calendars.
First, let's discuss how to compare time periods. Maybe you want to compare different time windows, or compare results versus the same week last year, etc. By combining CALCULATE and by using the FILTER function, we can generally find a way by utilizing a range of different parameters and logic.
In this particular tutorial, I dived into how you can complete this analysis successfully in Power BI.
In the next example, I will jump into how you can create YTD totals over a non-standard calendar. With the standard time intelligence functions, this would be seriously easy but here we have to implement quite different logic.
I showcase how you can structure your formula and how you can combine FILTER and CALCULATE together to create a YTD total.
Just two quick examples today around non-standard calendars but these are quite versatile patterns of time intelligence. It's just a matter of adjusting the parameters which may be inside these formula patterns.