# Power BI Financial Year To Date Calculations

In this tutorial, I’m going to talk about Power BI financial year to date calculations. Achieving a year to date analysis of a selected period is quite complex, but I’ll demonstrate to you a formula technique that enables you to quickly create a cumulative total over your specific financial year. You can watch the full video of this tutorial at the bottom of this blog.

I’m going to work with a simple model here. It’s pretty generic, but don’t get too caught up in the specific measures. The techniques are all the same especially if you’re doing financial year over anything like sales, quantity, revenue, costs, transactions, or forecast, for example. It’s all the same technique. You just need to sub-in the correct core measure into the actual pattern or formula combination.

So let’s dive into it.

## Calculating Power BI Financial Year To Date Total

First, I’m going to set up a generic table, so we can look at the data itself. I’m going to bring in my core calculation, which in this case is Total Sales. Getting things into tables, to begin with is key.

Now I’m going to show you how to do this financial year total. I’ll go create a new measure and call this Sales FYTD (financial year to date).

I’m going to use the CALCULATE function, which changes the context of the calculation. So I still want to calculate the Total Sales, but only for a financial year to date. This enables me to select what my financial year is, and then always be accumulating the amounts up to the end of the financial year.

Then, I have this time intelligence function called DATESYTD (dates year to date), which returns a set of dates in the year up to the current date. If I don’t specify something specifically for my financial year, it’s literally going to do the entire calendar year. And so, I put my Date column in there.

And then I want to specify the actual financial year. So maybe my financial year ends in June, right? But, it could also be at the end of March. So in this example, I put 31st of the third as the end of my financial year.

I push enter and I’ll drag this measure into the table, and you’ll see here that it is working out the financial year results. Now, remember that this can make it a bit tricky. I selected here on the slicer a calendar year, which is 2015. And so we’re actually seeing March 31st of 2015 as the end of the 2015 financial year.

Sometimes it’s easier to look at this within visualizations. So I’ll create an area chart here and you can see that we come to the 31st of March 2015, which is the end of our financial year.

## Financial Year To Date In A Slicer

A better way to set this up is to have a financial year within the slicer. It makes more sense. In my Date table, it looks like there’s a financial year already embedded.

And so, I’ll go to Transform Data and work out the financial date that was embedded into this Date table. In the Advanced Editor, you can see that this is a Date table that enables you to specify the financial year, and here it started in July. So let’s just go with that.

So I’ll go back to the formula and change the financial year-end to the 30th of the sixth. This way, we can align the actual financial year of the Date table.

With that, you can see the results in the visualizations changed as well.

Then, I’m going to bring in my financial year (FY) in the slicer instead of my Year. You’ll see that if I select one of these, it’s going to give me just the cumulative total for that particular financial year.

I can also do a multi-select and the results in the visualization changes as well.

## Utilizing The Analyst Hub

You can also utilize the Analyst Hub really effectively here. This is where you can format your calculations well. Just go to DAX Clean Up App.

Simply copy and paste your formula here, and it will automatically set it up in a way that is easier to read and understand. Then copy the newly formatted version of your formula by clicking on Copy Code.

Then you can paste the formatted formula back into your model like so.

You can also save the formatted version of your formula in the Analyst Hub and share it (click the share to community button) with other users. This way, you can easily reference it in any future calculations that you might do.

Now, if you want to have the ultimate Date table, I highly recommend you go to the Enterprise DNA forum and into the M Code Showcase. Here you can find the Extended Date Table (Power Query M function). I myself use this Date table pretty much in every development that I do.

And as you can see, it has a lot of views already. It’s one of the most popular pages on our site.

Click on the Extended Date Table (Power Query M function) and you can create your Date table using this code. Melissa, one of our Enterprise DNA experts, has created this really comprehensive M code that you can literally take and embed into your models.

## Conclusion

Calculating a Power BI financial year to date is just this easy. You could also do quantitative financial year to date or anything else at all. You just branch off from a different core measure and utilize the same technique.

As soon as you become familiar with calculating time intelligence functions, you’ll discover how amazing Power BI is. All the best with working through this one.

Cheers!

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.

## Microsoft Flows: Editing And Tracking In Power Automate

Once you understand how a Flow Diagram looks like, it will be easier to make some changes to it. In...

## Creating Power BI Reports Effectively & Avoiding Hidden Pitfalls

In this tutorial, I will discuss four hidden pitfalls in Power BI that can wreck your data model and/or...

## Power BI Page Navigation Buttons

The Power BI page navigation buttons play a critical role when it comes to storytelling. An organized...

## Power BI Report Examples And Best Practices – Part 1

In today's post, I'd like to present some Power BI report examples and best practices. In my own Power...

## Power BI Python Tutorial: How To Translate Texts

This blog will demonstrate how to perform language or text translation using Python and pipe it over...

## Measure In Power BI: Optimization Tips And Techniques

In this tutorial, you’ll learn how to optimize a measure in Power BI. Optimizing measures in your...

## How To Use Power Query Row And Column Selection

This tutorial will discuss how to use selection and projection inside the Power Query Editor. Selection...

## Use Tabular Editor To Create Calculation Groups In Power BI

Today's blog post will give you an introduction to calculation groups. I'll try to answer four basic...

## Effective Data Storytelling: Asking The Right Questions

To ensure that we have a good story to tell, effective data storytelling by asking the right questions...

## The Top 5 Power BI Alternatives in 2023

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

## Turning Calendar Type Layout Into Tabular Format In Power BI Using Query Editor

Analyzing the data that we have can be difficult if it is not correctly arranged. In this tutorial, I...

## Create A Lookup Table From Subtotals: Optimization In Power BI

In this tutorial, we’ll go through a technique that will create a lookup table from subtotals using the...