Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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
amitchandak
Super User
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

 

 

View solution in original post

5 REPLIES 5
amitchandak
Super User
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:

 

image.png

 

 

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

Mariusz
Community Champion
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.
LinkedIn

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.