Add a column for current and last Fiscal Year

[Anonymous]

Hi all,

in several reports I need to easily identify Current and Last Fiscal Year (FY) to avoid using the filter pane. In our company FY starts on Dec 1st and ends on Now 30th. I have already a calendar table with a FY_val field. What I'd need is to add one column to the calendar table to identify if a date is in Current FY (so a boolean value) and another to identify if it was in Last FY. 

I have already a measure calculating current FY:

Current_FY = 
LOOKUPVALUE ( 'Dim_Dates'[FY_val], Dim_Dates[Date], TODAY () )

but I cannot use a measure in a filter. Any advice on how two create the two calculated columns? Also is this the best practice for what I want to achieve?

[dadb25 · Accepted solution]

Hi,

You can use this calculated column : 

Check FY =
VAR CurrentFY =
CALCULATE (
MAX ( 'DateTable'[Fiscal Year] ),
'DateTable'[Date] = TODAY (),
ALL ( DateTable )
)
VAR DifFY = 'DateTable'[Fiscal Year] - CurrentFY
RETURN
SWITCH ( TRUE (), DifFY = 0, "Current FY", DifFY = -1, "Last FY", "" )

And you can use a slicer with the values of this column.

Regards

Mark my post as a solution if it helped you😀

[Anonymous]

Very elegant - it works perfectly.

Thank you!

C.