Forum Discussion
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:
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
5 Replies
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))) * 100Thank you amitchandak, your solution with the use of max is already better than what I tried to accomplish. However, how do I get the slicer to work? Right now it only shows the data for 2020, using a slicer or filter on the visual for year just gives me no results in 2019.
Preferably I'd like to have it like this (left is my formula, right is yours), but just being able to filter per year and it then calculating the correct index would be fine too. Mine currently gives me the 2020 value in both years, yours leaves 2019 blank (even if just 2019 is selected in filter/slicer). I've removed the revenue to post it here, the index is normally on the right of the revenue:
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
