cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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]), '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?
1 ACCEPTED SOLUTION
Super User

@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 5
Super User

@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
Not applicable

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:

Super User

@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
Not applicable

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!

Community Champion

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.