Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to dynamically divide years from the same column

Is there a way to dynamically divide through the years, if its the same data? I'm currently at:   Index = IFERROR(CALCULATE(CALCULATE(SUM([Revenue Column]), FILTER(ALL('general dim_Date'[Year]), '...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous , Your formula does not need an input. But this one can take input from slicer and work

    index = divide(CALCULATE(SUM([Revenue Column]),
    FILTER(ALL('general dim_Date'[Year]), 'general dim_Date'[Year]=max('general dim_Date'[Year]))) ,
    CALCULATE(SUM([Revenue Column]), FILTER(ALL('general dim_Date'[Year]), 'general dim_Date'[Year] = max('general dim_Date'[Year])-1))) * 100

     

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous , Not sure which formula visual is showing but this expected with formula I shared. if you have data only for 2020, 2019. So 2019 does not have prior and 2020 data is merged with prior year data.

 

Can share data in table format with expected output

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Oops my bad. I already filtered out 2018 in my query, causing the 2019 results to be empty due to having no 2018 data. Your formula works flawlessly, thank you!