# Power BI Custom Calendars: Calculating For Month On Month Change – 445 Calendars

This tutorial is about how you can run time intelligence calculations over custom calendars in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

This is really relevant to those who deal with 445 calendars, which is a very common scenario for Power BI users.

Not all businesses out there work to a standard calendar around financial years or calendar years. Sometimes all you have to work with is a customized 445 calendar. In this tutorial, I explain how you can start solving some of the time intelligence type analysis in Power BI with these custom calendars.

Unfortunately, you can’t utilize the built-in time intelligence features in the DAX language. They only work with standard calendars.

In this particular example, I will show you how to work out month on month change.

The techniques I go through also enable you, with some minor formula adjustments, to work out a week on week, quarter on quarter, or similar calculations of that nature.

But before we get there, we have to implement some logic, which is a little bit tougher, and it’s actually computing for the previous month sales.

## Solve For Previous Month Sales

First, let’s look at our table here. We have our Total Sales, which is a very simple calculation.

We simply put it in a particular context from our customer calendar. So we have Fin Year and Fin Month, and then it automatically filters the underlying fact table. And that gives us our Total Sales amount.

However, in this table on the left hand side, there’s a bit of an issue. We see here our Fin Month Number; but when we get to the end – the last week of the financial year of this particular calendar – the last day is 27th of April. It then jumps into the new financial year, but it’s not even in the new month.

This is where it gets a bit confusing. That’s why we have to build some logic to get the Previous Month Sales. We can’t just look at any particular month. In this case, we have to look at a particular number and work with this Financial Month Number column.

So I’m going to walk you through the formula, so you can get a good understanding of it.

## Walk Through The Formula

I’ve used Variables (VAR) in this calculation and I highly recommend it, because Variables enable us to break down these formulas.

First of all, we need to work out the Current Month that we are in, in any particular context, and that’s what the SELECTEDVALUE does. For example, if we are on the tenth month of 2014, that’s going to return the number 10.

If we look at the Current Year, it’s does exactly the same thing, while the MaxMonthNum literally works out the maximum month number. So we’re looking at the MAX Financial Month number across all of our calendar table, which is basically going to return the number twelve.

Now let’s focus on the core measure here, which uses the SUMX function. This formula sums up the Total Sales, but in a different context.

Logically, we’re trying to jump back to the previous financial year month and grab that number and bring it into the current context.

For example, in the row below, we have 2014 with month 10. We need to jump back to month 9 and drag this number (\$1.356M) into the tenth month, which is exactly what this logic in this formula is doing.

This formula is looking through the Calendar table, and if the Current Month is 1, then it will find the 12th month of the year before because that’s where we have to go and work out this dynamic calculation, MaxMonthNum.

In theory, we could just write 12 here because there’s always 12 months, but this is just another way to dynamically do it. We could use a similar technique if we’re doing a Week on Week change, for example.

This is going to evaluate to 12, but then we jump back and look at the year before. So if we’re in 2015 month one, we want to jump back to the twelfth month in 2014. And that’s how we drag that number down.

And if it’s not the first month, we jump back in the current year back one month, and that’s what this line of the calculation shows.

This may look a bit more complicated than it actually is, but it’s just a matter of getting your head around a few different formulas.

Now once we have our Previous Month Sales calculation, we can calculate our Month on Month Change. All we have to do is subtract Previous Month Sales from Total Sales. We put a BLANK logic to blank out those with no previous month sales.

From here, we can get the Month on Month Percentage Change. We simply divide Month on Month Change by the Previous Month Sales.

So that’s how we work out a Month on Month Change for a custom calendar.

## Conclusion

I hope with this tutorial, you can get a grasp of how to run time intelligence calculations for custom calendars and be able to apply it on your own data models.

It’s definitely worthwhile reviewing all these formulas in depth. There’s a little bit more to them, it’s just unfortunate that it’s not as easy as the time intelligence calculations for standard calendars.

The good news is that if you can understand how to implement these techniques, doing any time comparison work within Power BI will become much easier for you, especially if you have to deal with more customized calendars in your own data sets.

Cheers!

Sam

## Building a Data Visualization Portfolio – Showcasing Your Skills and Insights

Data visualization is a powerful tool that allows you to communicate complex information in a way that...

## Guide to Intermediate DAX Functions for Power BI

In today’s data-driven world, being able to use data analysis expressions (DAX) in Power BI and other...

## Optimizing DAX: Performance Tips for Power BI Reports

Data analysis in Power BI is not only about creating visually appealing reports but also about ensuring...

## DAX Table Functions Deep Dive

Explore an in-depth analysis of DAX table functions in Power BI, comparing SUMMARIZE and ADDCOLUMNS, and understanding INTERSECT and EXCEPT for enhanced data manipulation and analysis.

## Introduction to the RANKX and TOPN DAX functions in Power BI

One of the key aspects of analyzing data is to be able to rank or compare different elements based on...

## Power BI Visualization Technique: Learn How To Create Background Design Plates

Here's a Power BI visualization technique that you can utilize within your reports by using large...

## ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

## Calculate Average Per Customer Transaction Using DAX In Power BI

What I want to demonstrate in this tutorial is how we can calculate average sales, profits, or...

## Round Bar Chart – A Visualization For Your Dashboard

In this tutorial, you’ll learn how to create round bar charts for your Power BI report. It’s another...

## Conditional Formatting with Transparency Hex Codes

One of the best ways to implement conditional formatting is using the Field value option in the...

## Custom Theming In Power BI

For today's post, I'll do a quick review of the customizations you can make by directly editing and...

## Power BI Automation With Elgato Stream Deck

Today, I'm going to walk you through an outstanding Power BI automation using Elgato Stream Deck. We're...