# Year To Date Sales For Power BI Custom Calendar Tables

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

## Power BI Shape Map Visualization For Spatial Analysis

The Shape Map Visualization is probably my most favorite map visualization inside Power BI. I think it...

## Python Correlation: Guide In Creating Visuals

In todayâ€™s blog, we will walk through the process of visualizing Python correlation, and how to import...

## Microsoft Flow HTTP Trigger | A Power Automate Tutorial

A Microsoft Flow HTTP trigger allows users to trigger flows from third-party applications. In this...

## Data Visualization Tips For Your Power BI Reports

In today's blog post, I'm going to do another review of one of the submissions we had in the Power BI...

## How Will AI Affect Data Analysis in the Future

As the world becomes increasingly data-driven, the role of artificial intelligence (AI) in data...

## Showing Sales Growth In Power BI Reports

In this Power BI Showcase, we'll go through reports showing the Sales Growth analysis of a large...

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

## Creating Virtual Relationships In Power BI Using The TREATAS Function

The TREATAS function in DAX is one of the most interesting DAX formulas that you can utilise inside...

## Microsoft Power Query Tutorial On How To Fix Mixed Fixed Column Width Issues

Mudassir: For today, we have a very interesting problem to work with. The problem with this file is...

## MultiIndex In Pandas For Multi-level Or Hierarchical Data

MultiIndex in Pandas is a multi-level or hierarchical object that allows you to select more than...

## Power BI DAX ALL Function – How It Works

I want to give you a really quick introduction to the Power BI DAX ALL function. I find that there can...

## Sales Vs Budgets Insights â€“ Extended Budget Allocation Formula

In this tutorial, Iâ€™m going to show you an extended version of the ultimate Budget Allocation...