Filter By Year Dynamically

[Stuznet]

Hi,

I ran into a dead end that I like my graph display the Year from Current Year and to the following 5 consecutive year (2018-2022) dynamically without having me filter or slice it manully each time.

Here is the function I wrote. Then I dropped it into tooltips and filtered it by 1.

Column = IF([Year] >= YEAR(NOW()),"Future Year","")

Can someone help me?

[marcoslopez]

Hi @Stuznet, you could create a calculated column that shows 0 if year(date) < year(today) and the value with future dates.

Hope this help you, apologies for my bad english 

Best Regards, Marcos

[Stuznet]

@marcoslopezthank you for your input. I tried your suggested function.

Column = IF(YEAR([Date]) < YEAR(TODAY()),1,0)

I'm getting the same result as it shown on the chart from above. 

[v-yulgu-msft]

Hi @Stuznet,

Please add below measure to visual level filters and set its value to 1.

CheckMeasure =
IF ( YEAR ( SELECTEDVALUE ( Table[Date] ) ) >= YEAR ( TODAY () ), 1, 0 )

Best regards,

Yuliana Gu

[Stuznet]

@v-yulgu-msftI've tried the function you provided, unfortunately it is not the result I'm looking for. 

I filtered is "1".

.

I would like to see the result from 2018 to 2023 like it shown below the chart.

And so for next year start from 2019 to 2024. 

Thank you for your help.

[v-yulgu-msft]

Hi @Stuznet,

Please change the X-axis type from categorical to continuous for a test. If it still doesn't work, please share your .pbix file so that I can check for you.

Regards,

Yuliana Gu

[marcoslopez]

That I said was sth like having Column Total Invoices then 
New Calculated Column:
Total Estimated Invoices =
    if (
         year(InvoiceDate) >= year( today() );
         Total Invoices;
         0
        )

[Stuznet]

@marcoslopezI tried that and it giving false result.

Column = IF(YEAR([Date]) >= YEAR(TODAY()),[Total],0)

[PJHos]

Last 6 Years Filter = IF(AND(YEAR([Date]) >= YEAR(TODAY()) - 6,YEAR([Date}) <= YEAR(TODAY()) + 2), YEAR([Date}), 0 )

this is what iwe have to create a dynamic filter that looks at previous 6 years and future 2 years. Future dates as i use it in a forecast graph. Add it as a calculated column and apply as a filter, conditions for filter = 'is not 0'