Dynamic Forecast Year

[Stuznet]

Hi guys,

I have a Stacked Column Chart with Spend vs Fiscal Year data. The Total Amount columns are broken down into Category values. 

How do I make it so that only the 5 Fiscal Year by Amount are shown in graph?, and so for next year 2019 will display 2019-2024 

I wrote this function, Column

YearRank= RANKX(FILTER(Table1,Table1[ColumnYear] = EARLIER(Table1[ColumnYear])),Table1[Amount],,DESC,Dense)

Then I create a another column

Column = IF(Table1[YearRank] <= 5, [ColumnYear],"Wrong Date")

but I'm getting an error

Expressions that yield variant data-type cannot be used to define calculated columns.

I appreciate any help! Thank you!

[v-frfei-msft]

Hi @Stuznet,

One sample for your reference.

1. Create a calculated table.

Table = GENERATESERIES(1899,2100,1)

2. Create a measure as below.

Measure = var y = SELECTEDVALUE('Table'[Year])
return 
IF(MAX(Table1[year])>=y && MAX(Table1[year])<=y+5,1,0)

3. Create the Stacked Column Chart and add the measure to tooltip and make the visual filterd by the measure.

For more details, please check the pbix as attached.

Regards,

Frank

[Stuznet]

@v-frfei-msftThank you for providing your solution however it doesn't seem to work for me. I followed what you did but I'm getting false result 

[v-frfei-msft]

Hi @Stuznet,

Did you select any value in the slicer?

Regards,

Frank

[v-frfei-msft]

Hi @Stuznet,

Could you please share your pbix to me? You can upload the file to dropbox and share the line here.

Regards,

Frank