Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
PBI_hlpr1
Resolver I
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
dadb25
Helper I
Helper I

Hi,

dadb25_0-1701273913111.png

 

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😀

 

 

View solution in original post

2 REPLIES 2
dadb25
Helper I
Helper I

Hi,

dadb25_0-1701273913111.png

 

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.