# 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.

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

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## Related Posts

In this post, I want to show you what is, without a doubt, one of the most compelling visualization...

## Showcase Insights Using The Multi Threaded Dynamic Visuals Technique In Power BI

This tutorial will cover how to use the Multi Threaded Dynamic Visuals technique to create insights...

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Calculate Rolling Totals Using DATESBETWEEN In Power BI

Here Iâ€™m going to show you how you can calculate the amount sold between two different dates using the...

## Formatting DAX Code In Power BI

Effort here will pay dividends later Placing effort into formatting your DAX code will make a huge...

## Cumulative Totals Based On Monthly Average Results In Power BI

Today, I wanted to cover a unique technique around cumulative totals based on monthly average results...

## Power Automate Expressions – An Introduction

In this tutorial, weâ€™ll explore Power Automate expressions and how to use them in workflow automation....

## List.Max Power Query: User Guide With Examples

One of the most powerful and useful functions in Power Query M language is List.Max. It can be...

## R Scripting For Power BI Using RStudio

In this blog, weâ€™re going to go through the basics of R scripting for Power BI using RStudio. The...

## Simple Aggregations In Power BI

In this blog post, I'll touch on some simple aggregations in Power BI that you can use in your DAX...

## Microsoft Report Builder: Steps In Creating A List

In this tutorial, you will learn how to create a list and page break in Microsoft Report Builder. A...

## 5 Strategies To Enhance Your Power BI DAX Skills

In this tutorial, youâ€™ll learn the general strategies to improve your DAX or M capabilities. This is an...