Tabular Model – DAX Time Intelligence functions with an alternative Financial Year-ending
Recipe Overview
Preparation time: 10 minutes
Ingredients
- 1 Tabular Model
- Alternative Financial Year-Ending
- DAX Time Intelligence function
- 2 hints to avoid overcooking
Problem Statement
Many companies don’t follow the regular Calendar as we know (January 1st – December 31st). They follow their own Financial Calendar (often called Fiscal Calendar) which can start any time of the year.
Because of this, writing DAX Year-To-Date calculations for your Tabular Model might seem challenging.
In the step-by-step example, we are working for a company that starts its Financial Year on July 1st.
In this blog post, we will be answering the following questions:
- How can we create the YTD calculation starting from July 1st?
- How do we create the YTD PY calculation for the alternative Financial Year?
Step-by-Step Recipe
In this recipe, we are using the AdventureWorksDW database. In particular, we are going to use the Internet Sales and the Calendar table.
As you can see in the screenshot above, I have created a “Table” called Internet Sales Measures where I create and group the developed measures.
In there, I already created a measure called “Sales Amount”
As you can see, this measure can be used to calculate the SUM of Sales Amount. An easy report can look like this:
This visual shows the Sales Amount by Year and Month. As a next step, we want to add a column to this overview which is going to aggregate the Sales Amount of the Financial Year. In this case, it will start on July 1st and end on June 30th.
To get this done, we can use the TOTALYTD function in DAX. As you can see in the screenshot below it is adding up the Sales Amount from every month until December. This is the default behavior.
To get this result I used the following DAX code:
TOTALYTD (
[Internet Sales Amount],
'Calendar'[FullDateAlternateKey]
)
As a next step, we want to adjust the measure to calculate years from July 1st until June 30th. To get this done, we need to specify the end date of the Financial Year in the TOTALYTD function.
In the screenshot below you can see how I configured this:
As a result, I now get the following result:
As you can see in the screenshot above, the Internal Sales Amount FYTD measure always restarts from 0 in July of each year.
Good to know
When I started development on my new Tabular model, I created all my basic measures first before taking a look at Time Intelligence. When I created my first time intelligence calculation, I used the exact example mentioned in the article.
At that point, I ran into a couple of issues. I created the measure as I explained in the article but for some reason, it was NOT working.
Where did I make a mistake?
My first thought was that I forgot to create the relationship between my fact and dimension table. After some research, I found out that this wasn’t my issue. But what was the issue then?
“Make sure to mark your Calendar Dimension as Date Table”
As soon as I marked my Calendar dimension as my Date Table in my model it worked like a charm.
Enjoy!
Bon appétit!