Forum Discussion
How to dynamically divide years from the same column
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
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
Thank 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
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!