How to dynamically divide years from the same column

[Anonymous]

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]), 'general dim_Date'[Year]=YEAR(TODAY()))) / CALCULATE(SUM([Revenue Column]), FILTER(ALL('general dim_Date'[Year]), 'general dim_Date'[Year] = YEAR(TODAY())-1)) * 100),BLANK())

But that just returns values for the current year, so changing the filter to 2019 would still give me the index of 2020. Can I make this calculation dynamic?

[amitchandak · Accepted solution]

@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]

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:

[amitchandak]

@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]

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!

[Mariusz]

Hi @Anonymous 

Any chance you can provide a data sample and an outcome that you expect?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn