Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

Year To Date Sales For Power BI Custom Calendar Tables

by | 9:00 am EDT | June 16, 2020 | Power BI

We’re going to dive into some custom Power BI calendar tables in this tutorial. Many organizations don’t work by the standard calendar date – they might work by a financial week number or by a custom week number. You may watch the full video of this tutorial at the bottom of this blog.

However, the time intelligence calculations in Power BI just don’t work with these custom or non-standard calendars. In this tutorial, I’ll show you what I mean by this. In this demonstration, we’ll calculate the year-to -date (YTD) number based on the week, specifically for custom Power BI calendar tables using some DAX techniques.

Why Time Intelligence Calculations Don’t Work On Custom Calendars

In this table, we have information from 2013. We have the Financial Month Number, Financial Week Number, Week of Quarter, and Calendar Week Number.

However, if we come to the end of the year, the Calendar Week Number goes over the end of the year; so, we have two days inside of 2013 that are in week 1, and the rest are in 2014.

This is where we can run into trouble because the time intelligence calculations work directly over a calendar date. We can’t utilize any of the good time intelligence functions, as none of them will work. So we have to create a custom logic, usually by using the FILTER function to work out these numbers.

But before we jump into the YTD number for custom calendars, let’s have a quick look on how it’s calculated for a standard calendar.

A Standard Date Calendar Calculation

In this example, we assume that our financial year started in May and we wanted to run a cumulative total for that financial year. This is how it’s done on the standard date calendar.

All we have to do here is put in the Date column, and then we type in 30/4, which signifies that it’s the last day in the financial year. Once we apply that to the date context, we get the correct result. It just accumulates through time, and then rounds off at the end of April 2014 as the total for that financial year.

It becomes tricky when we bring this calculation into this table on the right, which only has the Financial Year and the Financial Week numbers. It calculates correctly, but we run into an issue because the date column does not overlay perfectly over the Financial Week number.

Anything in 2015 would be wrong and so we can’t use this calculation for custom calendars.

Calculating YTD Sales For Non-Standard Calendars

So, let’s create a new measure and call it Total Sales YTD – Custom. We’ll use variables (VAR) to make things easier and more intuitive. Our variables are CurrentFinWeek and CurrentFinYear – here we use SELECTEDVALUE.

The second part of the calculation is where we write the logic. We’re still calculating Total Sales, but in a different context to what the normal time intelligence calculations do. That’s why we use FILTER, which solves most of these custom calendars. We also use the ALL function, and then go Calendar Daily, which releases any context on the calendar. After that, we’re going to re-apply the context based on the logic here.

The ‘Calendar Daily’ [Fin Week Number] is going to iterate through every single Financial Week number, but we want to always have the current Financial week number in a particular row where that evaluation is happening.

Now, if we drag this in, we see that this calculates the correct result for us based on a custom calendar.

Conclusion

This tutorial highlights an effective technique that you can use when working with a Power BI calendar, specifically one that does not follow the standard date calendar. You cannot use the time intelligence formulas with these non-standard calendar tables, which is why I’ve created the logic to solve this.

I hope you find this blog useful when dealing with custom calendars. Check out the video and links below for more details on this tutorial and other related resources.

All the best!

Sam

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

Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...

Power BI Import vs Direct Query: Which is Better & Why?

In the world of data analysis, Power BI offers you a range of tools to connect to your data sources....

Power BI Certification: Everything You Need to Know

In today's data-driven world, the ability to transform raw numbers into meaningful insights is more...

Power BI Bookmarks: The Ultimate Guide

When working with data, bookmarks offer a streamlined and personalized way to navigate through large...

Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...