Previous Fiscal Year automatically

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