Calculate Financial Year To Date (FYTD) Sales In Power BI Using DAX

There will be countless times when you will be required to calculate your sales financial year to date, but only the standard date table is available. You may watch the full video of this tutorial at the bottom of this blog.

One thing that can seem like a limitation of Power BI is its calendar date table. You may initially think that you need a new date table, but you don’t.

You can use DAX in either measures or calculated columns to get your calculation to work around financial years.

This post showcases how you can calculate a cumulative total that works across your financial year.

Table of Contents

Using DATESYTD In Our Calculation

The first thing that we need to know here is that there is a very powerful time intelligence functions set that will enable us to calculate cumulatively. And this is the DATESYTD.

But DATESYTD is not limited to calculating cumulative totals within a calendar year. By using this inside the CALCULATE statement we can almost effortlessly move from a calendar year calculation to a financial year one.

It’s really not that difficult in this case. You just need to understand what CALCULATE does and then how to apply time intelligence functions.

Now, let’s dive into the first step of our calculation.

Calculating Sales Year To Date

To calculate financial year to date, we’re going to start off by calculating our sales year to date.

What we have here is a total sales table that is filtered by month and year and starts in 2014.

total sales table filtered by month and year

Since we are summing up our total sales column in this table, we have this simple measure that is summing up our total revenue.

total sales summing up the total revenue

Now that we have our total sales measure, we’re going to use CALCULATE to change the context of our calculation.

We, therefore, need to create a new measure which we will call Sales YTD or sales year to date.

As I’ve said, I’m going to use CALCULATE then place in the Total Sales expression.

Next, I’m going to add DATESYTD and then put in the sales column that’s in our dates table.

Our Sales YTD formula will be like this one below.

After dragging in our new measure, we now have the sales year to date in our dates table.

You’ll notice that the sales cumulatively goes up starting off with 4.5 million in June 2014 to 35 million in December 2014.

It then goes down again to the total monthly sales in January 2015 before it cumulatively goes up again.

This pattern will continue up to the latest date that we have our sales data.

Calculating Sales Financial Year To Date

Since we already have our sales year to date, it’s now very easy to calculate our financial year to date.

It’s like having already laid out the foundation which makes it easy to move on to the next steps which I always prefer when working in Power BI.

So what we need to do is to just make a few tweaks in our formula.

First of all, let’s change the name of our measure from Sales YTD to Sales FYTD.

Then let’s modify our DATESYTD field. If we add a comma here, we get the [YearEndDate] option.

sales financial year to date with yearenddate option

What we need to add here is the last day of the month where we want our cumulative calculation to end.

So let’s say we want the cumulative totals to start in July and end in June the following year, we’re going to add “30/6” in the DATESYTD field to get this formula.

sales financial year to date with yearenddate

If we look at our table now, you’ll see that this is exactly what we have.

sales table with sales financial year to date

You may, of course, have a different month for your last date. It can be March or October or whatever that is.

The process remains the same. You just need to add the last day and the month in the DATESYTD field and then you’ll have your financial year to date.

Sales Financial Year To Date Visualization

Like any other table that we create in Power BI, we can definitely turn this one into a visualization.

Let’s look at the example that we have here.

sales financial year to date filtered by month and year visualization

Our calculation of the cumulative total starts in June 2014 so the graph goes up until March 2015 which is the last month in the financial year.

It goes down to show just the total sales in April 2015 and then climbs all the way up until March 2016 before going down again in April and so on.

One of the things I really like about creating visualizations in Power BI is the range of options that we have in presenting our data.

In the first visual that we created, the beginning of our financial year shows the sales of the first month. But if you want your graph to start with zero in the first month, you can easily do so.

All you need to do is to filter the data by Date instead of by Month and Year.

Our visualization will show exactly that. So you see that this one is filtered by Date as shown in the upper left corner. We start from zero at the beginning of our financial year and goes up until the last date before going back again to zero.

sales financial year to date filtered by date visualization

We always have a wide variety of options when creating our reports in Power Bi and it’s usually just a matter of choosing which one will be the best way to present our data.

Conclusion

Calculating your financial year to date when you have a calendar date table is really easy.

In this post, you have seen how using the DATESYTD functions set has allowed us to calculate cumulatively not only our sales year to date but also our sales financial year to date.

You may also want to explore how you can make sure that your months and quarters are all aligned with your financial years. This is done via calculated columns and will be something I’ll dive into in another post.

Cheers,