# 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

## MAXX in Power BI – A Detailed Guide

A hands-on guide to implementing data analysis projects using DAX, focused on the MAXX function and its combinations with other essential DAX functions.

## Leveraging the COUNTX Function In Power BI

Learn how to leverage the COUNTX function in DAX for in-depth data analysis. This guide provides step-by-step instructions and practical examples.

## Using the FILTER Function in DAX – A Detailed Guide With Examples

A comprehensive guide to understanding and implementing the FILTER function in DAX, complete with examples and combinations with other functions.

## DATESINPERIOD Function in DAX – A Detailed Guide

Learn how to implement and utilize DAX functions effectively, with a focus on the DATESINPERIOD function.

## Using the DISTINCT Function Effectively in DAX

A systematic exploration of the DAX DISTINCT function to optimize data analytics.

## Guide and Many Examples – ALL Function in DAX

A detailed guide to understanding, implementing, and mastering the DAX ALL function, complemented by practical examples and combinatory techniques.

## Detailed Guide to SWITCH function in DAX

A comprehensive guide to mastering DAX functions in Power BI for conducting advanced data analysis.

## SUMMARIZE Function in DAX – A Deep Dive

A comprehensive guide to using the DAX function SUMMARIZE in Power BI, with detailed explanations and practical examples.

## Your Data Visualization Doesnâ€™t Look Great. What Should You Do?

Data visualization is the key to unlocking the insights hidden within your data. But, what if your...

## Leveraging Power BI for Data-Driven Decisions

In the world of data analytics, thereâ€™s a constant demand for tools that not only help you make sense...

## Understanding Data Models and Visualizations

Power BI is a robust and versatile data visualization tool that has gained popularity for its...

## Getting Started with DAX in Power BI: A Beginnerâ€™s Guide

Data analysis expressions (DAX) are the key to unlocking the superpowers of Power BI. If you want to...