Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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😀
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😀
Very elegant - it works perfectly.
Thank you!
C.
User | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |