Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Power BI Financial Year To Date Calculations

by | 7:00 pm EDT | March 24, 2021 | Power BI

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

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

## How to Interpolate in Excel: User Guide With Examples

In data analysis, interpolation plays a crucial role in estimating values that fall between known data...

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