

Power BI’s strong data modelling language, DAX, includes many functions that make it easy for report authors to report on revenue/sales/cost or any other numeric KPI sliced by years, quarters, months, weeks etc., and allowing to compare periods like MTD (Month To Date) or YTD (Year To Date) to the same period last year. This is very handy and very common, as many businesses want to be able to compare their performance between similar periods over time and visualise the trends.
At the same time, many organisations do not use the standard years we all do, but rather use financial years or fiscal years, that often start and end on different time frames. Two of the more popular options I’ve seen are years that start in July and end in June, and years that start in April and end in March. In this article, I’ll use an example of a financial year that starts on April 1st and ends on March 31 the next year. It gets the year number of the latest actual year. So for example, anything from April 1st, 2019 until March 31st 2020, is referred to as 2020, or FY2020.
If your organisation wants to use Power BI to report on performance, based on financial years and not on normal years, some of the Power BI DAX functions cannot be used. In this article I’ll show how to create three calculated measures : sales of the current financial year, sales of the previous financial year, and sales of the previous financial year YTD (from the beginning of that year, until today’s date, last year). As mentioned, I’ll assume a financial year that starts in April and end in March of the next year.
The first thing we need in our model, is a date dimension table that includes a Financial Year column. There’s plenty of content online on how to create a date dimension, including my own article here. Here’s a screenshot of what I’m using in my model:
For simplification of the examples, let’s assume that our model includes two table only, the date dimension we just talked about, and a sales table:
As you can see, the sales table includes simple fields: Customer, DateID (which links to the date dimension), and a Sales Amount field. The other fields you see above are calculated measures, some of which I’ll show you how to create.
Let’s start with the calculated measure Current FY YTD Sales Amount. This measure looks to summarise all sales amount from the beginning of the current financial year, until today. For example, it is July 13th 2019 when I write this article, which means we are looking for values of FY2020, between April 1st 2019 and today – July 13th 2019. Here’s the DAX expression I’ve used:
Current FY YTD Sales Amount =
VAR v_current_month=MONTH(TODAY())
VAR v_current_year=YEAR(TODAY())
VAR v_current_fy=SWITCH(v_current_month,1,v_current_year,
2,v_current_year,
3,v_current_year,
v_current_year+1)
RETURN
CALCULATE (
SUM ( ‘Sales'[Sales Amount]),
‘Dim Date'[Financial Year] = v_current_fy
)
The DAX measure code starts with setting two variables – today’s month into v_current_month, and today’s year into v_current_year. We then check if the current month is January, February or March. If it is one of them, the financial year is equal to the current year. Otherwise, it is equal to the next year. The result of that check (using the SWITCH function) is stored in a third variable, v_current_fy. Once we’ve figured out what is the current financial year, we can use it to filter values in the sales table and return a summarised figure of all sales that happened on days with financial year equal to the value in v_current_fy. Note that we didn’t have to verify that the sale date is until today and not in the future, because I’m assuming the system does not hold future sales. If yours does, a simple modification can be added, to filter out any records where the date is higher than today. In a similar way, let’s look at two additional measures that look very similar – one summarises sales amount for the previous financial year (the entire year), and the other one does the same, but only YTD, meaning from the beginning of the year until today less 365 days.
Previous FY Total Sales Amount =
VAR v_current_month=MONTH(TODAY())
VAR v_current_year=YEAR(TODAY())
VAR v_previous_fy=SWITCH(v_current_month,1,v_current_year-1,
2,v_current_year-1,
3,v_current_year-1,
v_current_year)
RETURN
CALCULATE (
SUM ( ‘Sales'[Sales Amount]),
ALL ( ‘Dim Date’ ),
‘Dim Date'[Financial Year] = v_previous_fy
)
Previous FY YTD Sales Amount =
VAR v_current_month=MONTH(TODAY())
VAR v_current_year=YEAR(TODAY())
VAR v_previous_fy=SWITCH(v_current_month,1,v_current_year-1,
2,v_current_year-1,
3,v_current_year-1,
v_current_year)
RETURN
CALCULATE (
SUM ( ‘Sales'[Sales Amount]),
FILTER (
ALL ( ‘Dim Date’ ),
‘Dim Date'[Financial Year] = v_previous_fy &&
‘Dim Date'[Date]<=TODAY()-365
)
)
Then, you can use these three calculated measures to report on current vs. previous year’s sales amount:
I hope these examples are of some help. Obviously previous experience in Power BI and DAX is needed. Please get in touch if you need help implementing it, or if you are facing similar Power BI challenges. If you enjoyed the contents of this article, please consider sharing it with your network on LinkedIn. As an independent consultant, this helps my business. Thank you!
2 Comments. Leave new
The above is good but did not work, DAX returned “BLANK’
Sorry it didn’t work, returns “BLANK”, can u help me figure it out?