topic Re: Calculate previous fiscal year in DAX Commands and Tips

Calculate previous fiscal year

dannyd — Sun, 03 Jan 2021 15:58:36 GMT

Hi,

I'm struggling to figure out how to calculate the spend for the previous fiscal year.

My current measure looks like this:

Spend Previous FY = CALCULATE( ([Spend Actual Current Month]), All('Date'), 'Date'[Fiscalyear] = YEAR(TODAY())-1 )

The problem i'm having is that YEAR(TODAY) refers to the actual calendar and not the fiscal year calendar.

Any tips on how to manage this?

Re: Calculate previous fiscal year

MattAllington — Sun, 03 Jan 2021 21:11:24 GMT

The easiest approach is to use the inbuilt time intelligence formulas

Spend Previous FY = CALCULATE( ([Spend Actual Current Month]), SAMEPERIODPRIORYEAR('DATE'[Date]) )

this assumes your calendar table is at the day level and follows the calendar table rules

https://exceleratorbi.com.au/power-pivot-calendar-tables/

instead of using TODAY(), you use a slicer to select a date, and the formula will work.

Re: Calculate previous fiscal year

amitchandak — Mon, 04 Jan 2021 01:41:21 GMT

dannyd , You can get that with help from date table and time intelligence.

replace 12/31 in case of datesytd with end of your year

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

refer Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Re: Calculate previous fiscal year

petelanglois — Mon, 28 Feb 2022 19:51:58 GMT

I'm having the same issue - amitchandak: your solution works but only at the year granularity. I need to be able to look at any period - year, quarter or month - to see the selected vs previous year values. Here are the results I'm getting:

The DAX is summarizing the previous year for every month, however I need to be able to use the custom date table's 445/fiscal calendar, which I'm struggling with. I attached a copy of the fiscal calendar for few years in case you have any pointers.

I was able to successfully convert this calendar into a 445 calendar in power bi, and renamed the columns to

FiscalYear

FiscalQuarter

FiscalMonth

However, when I use Dateadd to grab this fiscal year vs last fiscal year, it doesn't work, because actual dates of the fiscal year periods don't match up to one another, even though they have the same number of days in each.

For example Fiscal Q1 in 2020 has the dates 2/2/2020-5/2/2020 however Fiscal Q1 in 2021 has the dates 1/31/2020-5/1/2021. I'm completed stuck!

Re: Calculate previous fiscal year

Unknowncharacte — Thu, 02 Nov 2023 13:50:41 GMT

This was very helpful, thank you!