Previous Fiscal Year automatically

[Anonymous]

Hi Power BI Community!
I have these two cards:

Fiscal Year Measure =  TOTALYTD([Rate (monthly)],Dates[Date],"9/30") 

My Fiscal Year starts at 01.10 till 30.09. Everything works right but now I also want to show the last Fiscal Year.

I use the advanced filter on date = Date is before 01.10.2020. But this will not work for next fiscal year.
The relativ date filter does only have year and calendar year but not Fiscal Year.

What can I do?

[ERD · Accepted solution]

Hi @Anonymous ,

You can try this

Prev FY =  
CALCULATE (
    [Rate (monthly)],
    SAMEPERIODLASTYEAR ( DATESYTD ( Dates[Date], "9/30" ) )
)

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[BA_Pete]

Hi @Anonymous ,

You'll need to set up a calendar table that has fields for your fiscal year in it. Relate calendar[Date] to factTables[Date].

Your fiscal year field will look something like this when implemented in Power Query:

addFinYear = Table.AddColumn(addDateKey, "finYear", each Date.Year([date]+#duration(275,0,0,0))),

In this example, the '+#duration(275,0,0,0)' part is adding 275 days to the calendar row date as my fiscal year increases on 1st April of the calendar year. You can adjust the number of days here to something like 92 (maybe 93) to increase the fiscal year at 1st October on the calendar row.

Once you have that, you create a relative fiscal year field something like this:

addRelativeFY = Table.AddColumn(addFinYear, "relativeFY", each [finYear] - Date.Year(Date.Today+#duration(275,0,0,0))),

Again, you adjust the #duration section to the number of days between 1st October and 1st January.

You can then use this relative fiscal year field in filters and calculations, for example:

_salesLY = 
CALCULATE(
  SUM(yourTable[Sales]),
  calendar[relativeFY] = -1
)

Setting up a proper calendar table and relating it to your fact tables will also allow you to use time intelligence functions such as SAMEPERIODLASTYEAR if you just want the relative LY period, just make sure to use the calendar[Date] field in function arguments instead of factTables[Date].

Pete

[Washivale]

@Anonymous : try with

Last FY : Calculate([Fiscal Year Measure],SAMEPERIODLASTYEAR(Dates[Date]))