Forum Discussion

Stuznet's avatar
Stuznet
Helper V
7 years ago

Dynamic Forecast Year

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!

 

Need Help
Show and Tell
Tutorial Requests

4 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    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's avatar
      Stuznet
      Helper V
      7 years ago

      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's avatar
        v-frfei-msft
        Community Support
        7 years ago

        Hi Stuznet,

         

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

         

        Regards,

        Frank