Date FY Slicer to only show Last 3 Years, Current Year and 3 Years in the future

[sabilahmed]

Hi all,

I have the slicer below from my Dim_Calendar table:

As you can see, it is displaying all years because the CALENDARAUTO deature is reading lots of dates across all the data in the model.

Is there a way to restrict the slicer so it always shows:

Previous 3 FY

Current FY

Future 3 FY

So 7 FY's in total.

Thanks,

Sabil

[memepu · Accepted solution]

Hi @sabilahmed ,

Please refer to My steps to see if it helps you.

Create a year column first.

yeat = YEAR('Table'[Date])

 Then create a column put into the slicer.

Column =
VAR _1 =
    YEAR ( TODAY () )
VAR _qian3 = _1 - 3
VAR _hou3 = _1 + 3
RETURN
    IF ( 'Table'[yeat] >= _qian3 && 'Table'[yeat] <= _hou3, 'Table'[yeat], BLANK () )

Best regards.

[amitchandak]

@sabilahmed , you have to create a flag and use that filter.

Get a year for you FY using start FY date or end FY Date

Then create a flag

new column =

Var _max = maxx(filter(Date, date[Date] = today() ) , [FY]) //Here FY is a number

return

if([FY] >= _max -3 && [FY] <= _max+3, 1, 0)

refer this for FY start date

https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5e...

or

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...