DAX | Power BI | Time Intelligence |

Time Comparison For Non Standard Date Tables In Power BI

Running time comparison type analysis on custom calendars is a little bit more complex than if you were using a standard calendar. You may watch the full video of this tutorial at the bottom of this blog.

The reason behind this is because the time intelligence functions available for custom calendars allow users to quite easily and effectively write a formula and achieve the results that they want. Unfortunately with non-standard calendars, for example, a 445 calendar, you have to actually write some additional logic to be able to achieve the most time intelligence or time comparison type analysis.

In this tutorial, I’m going to demonstrate some pure time comparisons. I’ll take you through how you can compare one time period to another , based on a week or a number and not a particular date.

As a result, you can and will be able to utilize what you learn across various time horizons. Additionally, I’m going to go a step further and really dive into how can we analyze from one week to another across any time period. For instance, it could be one week of a previous month or a week to the same month last year.

So first, I’m going to show you what happens when you use the time intelligence calculation (DATEADD) and why it won’t work. We have to use some custom logic to actually get it working.

Table of Contents

Time Intelligence For Custom Calendars

It’s easy to do a time comparison with time intelligence functions. In our formula for Sales LY, for example, we have the DATEADD function, which basically does all the time in comparison. We can do a calculation for a day, month, quarter, and year. In this case, we are showing year.

This formula works well for a standard calendar. As we can see from our table, it calculates correctly the same day in the next year.

However, when we use the same formula for a custom calendar, where we have say just a year and just a week to work with, it doesn’t work correctly.

We can see this in the very first week of 2015. Remember that this doesn’t align to any calendar week since we are doing a custom calendar, so the first of this month in this financial year does not actually align through this first week.

In theory, you would think that this amount is going to be the same as that of the first week in 2014, but it’s not because of the misalignment and the overlaying of the dates on this financial week number, so we need some custom logic in here to make this work.

Time Comparison Analysis For Custom Calendars

Now let’s go through the logic that could solve this problem. This will be applicable to any custom calendar table. The technique is just the same. Once you get to understand how it’s done, you can easily apply it to your own model and Power BI reports.

In this calculation for our Sales LY – Custom, we use Variables (VAR), as it simplifies things a lot. Then, we use SELECTEDVALUE to bring in our week and year. And then we write our formula, where we still use CALCULATE Total Sales, and then put the logic inside.

We use FILTER ALL Dates (Calendar Daily), the entire table here. Then, we write our logic, working out whether our financial week number is equal to the current financial week. This is how we compare one financial week one year to the year before. And so then, we also isolate the year by having our VAR for year (CurrentFinYear) subtracted by 1.

This technique allows us to jump back from 2015 to 2014 to get that week’s number and bring it into the current context of our results. We then place it inside of this table and we’ll see now that the number or amount is correct.

If we jump back to the first week, we see the exact same amount.

This logic has achieved what we wanted to achieve. And then from here, we can branch out to get more insights.

Conclusion

This example is applicable to any custom table. All you might need is to replace the variables, depending on what time comparison you want to have, but it’s always going to be similar logic.

There are a lot of different ways that you could ultimately use this technique. It is crucial to really try and learn it well. Because the truth is that by combining all of these DAX formulas, you can actually achieve a lot of things, not just this particular insight but many others as well.

All the best!

Sam