cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Resolver I

## Add a column for current and last Fiscal Year

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?

1 ACCEPTED SOLUTION
Helper I

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😀

2 REPLIES 2
Helper I

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😀

Resolver I

Very elegant - it works perfectly.

Thank you!

C.