Calculate previous fiscal year

[dannyd]

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?

[petelanglois]

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!

[amitchandak]

@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.

[Unknowncharacte]

This was very helpful, thank you!

[MattAllington]

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.