# 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

## How To Install DAX Studio & Tabular Editor In Power BI

In this tutorial, you'll learn how to download and install DAX Studio and Tabular Editor 3 in your...

## R And RStudio Download And Installation Guide

This tutorial will guide us on how we can download and install R and RStudio which are both free and...

## Launching The Enterprise DNA Membership License

Today we launch a brand new offering from Enterprise DNA.  After a tremendous...

## Iterating Functions In DAX Language – A Detailed Example

For this blog post, I want to dive into iterating functions within the DAX language in Power BI. The...

## Using Power BI DAX Functions To Deal With Products That Have Changing Prices Overtime

In this blog post, we are going to work through an advanced but real-world analysis involving Power BI...

## Power BI Dashboard Designs: Visuals And Effects

An effective dashboard design presents data in a concise, engaging, and powerful way. The presentation...

## DAX Measures In Power BI Using Measure Branching

Build DAX measures using measures. This is what I call measure branching. This technique is one of the...

## Temporal Scale Using Calculated Columns In Power BI

Every so often weâ€™ll be needing the availability of a custom visual of a bar chart or line chart that...

## First N Business Days Revisited – A DAX Coding Language Solution

Let's take another look at the problem discussed in this tutorial, which dynamically compared the first...

## Junk Dimension: What Is It And Why It’s Anything But Junk

Today, I want to talk about a data modeling concept called junk dimension. From its name, you'd think...

## Power BI Slope Chart: An Overview

In this tutorial, we'll be looking at a not-so-common custom visual called the Power BI slope chart....

## Create Power BI Reports With These Techniques & Examples

For todayâ€™s blog, I want to walk through a couple of amazing reports and share some of the tips I have...