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.

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.

time comparison

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.

time comparison

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.

time comparison

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.

time comparison

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

***** Related Links *****
Calculate Previous Weeks Sales – Advanced DAX in Power BI
Year To Date Sales For Non Standard Calendar Tables – DAX In Power BI
The DATEADD Function: The Best And Most Versatile Time Intelligence Function in Power BI

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

Enterprise DNA Power BI On-Demand

The Latest

As you continue your journey as a Python programmer, you’ll want to write code that is more efficient, readable, and…

Python Inline If: Simplify Your Conditional Expressions

You’ve been cruising through your Python journey, slicing through lists, taming those wild tuples, and maybe even wrestling with a…

Python Empty String: Understanding and Handling It Effectively

Power BI financial dashboards provide a quick and easy way to monitor an organization’s financial performance in real-time. By consolidating…

Power BI Financial Dashboard Examples: Key Insights for Businesses

When working with integers in Python, you should know the maximum value your code can handle. This will depend on…

Python Max Int: Understanding Maximum Integer Limits

Pi is a fundamental mathematical constant that represents the ratio of a circle’s circumference to its diameter. Leveraging Pi in…

4 Ways to Use Pi in Python With Examples

ChatGPT is an advanced AI-powered tool that can transform the way you write code. Developed by OpenAI, ChatGPT accelerates your…

ChatGPT for Coding: A Guide With Practical Examples

When working with data projects in Python, you are most likely to use datetime functions. Almost every dataset that you’ll…

Python datetime, a comprehensive guide with examples

Power BI is a powerful business analytics tool that helps you visualize and analyze data from various sources. One of…

Power BI Themes: How to Customize Your Reports with Ease

With the advent of ChatGPT, individuals and businesses worldwide have been using it to simplify their daily tasks and boost…

ChatGPT for Data Scientists: Unleashing AI-driven Insights

Staying ahead of the curve in data analysis is essential to your success in business. One of the most innovative…

ChatGPT for Data Analysts: Revolutionizing Insights and Reporting

Imagine being able to look at your data from every which way — from the bird’s eye view right down…

Power BI Hierarchy: Unlocking Levels and Drill-Downs in Visualizations

As you explore Python and take on more complex projects, you’ll notice that some projects have lots of files. Managing…

os.path.join: Simplifying File Path Operations in Python

Load More