Dynamic Percentage

[Anonymous]

Hello Everyone,

I need to calculate the percentage change in price based on the selected date range in a slicer. For example, if I select the range from 2014 to 2016 for Code A, the percentage change should show 0% at the start of 2014, with the following changes reflected from that point onward. Similarly, if I change the selection to 2017 to 2019 for the same code, the percentage change should reset to 0% at the start of 2017 and calculate the changes accordingly based on that selection.

Please click on the link to check : My Drive - Google Drive


I have tried to use following dax but its showing 0% at all level.

Measure =

VAR __firstnoblankdate = FIRSTNONBLANK(ALLSELECTED('Calendar'[Date]),SUM(Sheet1[Price]))

VAR __base_value = CALCULATE(SUM(Sheet1[Price]),'Calendar'[Date]=__firstnoblankdate)

VAR __cur_value = SUM(Sheet1[Price])

VAR __result = DIVIDE( __cur_value - __base_value, __base_value)

RETURN

__result

[Kedar_Pande]

Create Measures for Starting Price and Current Price:

Starting Price = 
CALCULATE(
FIRSTNONBLANK(Main[Price], 1), 
FILTER(
Main,
Main[Date] = CALCULATE(MIN(Main[Date]), ALLSELECTED(Main[Date]))
)
)

Current Price = 
CALCULATE(
LASTNONBLANK(Main[Price], 1), 
FILTER(
Main,
Main[Date] <= MAX(Main[Date]) && Main[Date] >= MIN(Main[Date])
)
)

Percentage Change Measure:

Percentage Change = 
VAR StartPrice = [Starting Price]
VAR CurrentPrice = [Current Price]
RETURN
IF(
ISBLANK(StartPrice),
BLANK(),
DIVIDE(CurrentPrice - StartPrice, StartPrice, 0) * 100
)

[Anonymous]

Hi @Kedar_Pande 

Thank you for the reposne.
I tried with your method but its still not showing the result.
For your refernce I am giving the link so you can have look on data.
My Drive - Google Drive

[Anonymous]

Hi @Anonymous ,
Unfortunately Due to environmental reasons, we can't open the Pbix file you uploaded, we try to use the sample data to solve your problem, we modified the original basis of your code, and got the following results Hope it will help you!

Measure 2 = 
VAR BaseDate = MINX(ALLSELECTED('Table'), 'Table'[Date])
VAR BasePrice = CALCULATE(SUM('Table'[Price]), 'Table'[Date] = BaseDate,ALL('Table'))
VAR CurrentPrice = SUM('Table'[Price])
VAR Result = DIVIDE(CurrentPrice - BasePrice, BasePrice, 0)
RETURN 
Result

If you have any other questions, you can check the pbix file I uploaded, I hope it will help you, if you have any further questions, you can contact me anytime, I will get back to you as soon as I receive the message!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @Anonymous 

I tried with your given DAX but its stilll not working and I dont know how to attach file in reply.
could you let me know how to attache the file so that i can share with you the sample file that i have created?

[Anonymous]

Hi @Anonymous ,
Greetings! I see you mentioned that you don't know how to upload data. Here is a link that will help you step by step on how to do it:How to provide sample data in the Power BI Forum - Microsoft Fabric Community. I hope this helps you! If you have any other questions, please feel free to contact me!

[rajendraongole1]

Hi @Anonymous - Can you check below modified dax formulae to calculate the percentage change in price based on the selected date range in a slicer, with the percentage resetting to 0% at the start of the selected period

Measure =
VAR __firstnoblankdate = FIRSTNONBLANK(ALLSELECTED('Calendar'[Date]), BLANK())
VAR __base_value = CALCULATE(
SUM(Sheet1[Price]),
'Calendar'[Date] = __firstnoblankdate
)
VAR __cur_value = SUM(Sheet1[Price])
VAR __result = DIVIDE(__cur_value - __base_value, __base_value, 0)
RETURN
__result

Hope it works on reset part. 

[Anonymous]

Hello Rajendra,
Thank you for you response.
I tried you suggested measure but its still not working.
its showing 0% at all dates.

[rajendraongole1]

Hi @Anonymous - I am not able to download  the pbix file from drive . you can please share pbix file in right way?

can you please try the below modified logic

Percentage Change =
VAR FirstSelectedDate =
CALCULATE(
MIN('Calendar'[Date]),
ALLSELECTED('Calendar')
)
VAR BaseValue =
CALCULATE(
SUM(Sheet1[Price]),
'Calendar'[Date] = FirstSelectedDate
)
VAR CurrentValue =
SUM(Sheet1[Price])
VAR Result =
DIVIDE(CurrentValue - BaseValue, BaseValue, 0)
RETURN
IF (
NOT (ISBLANK(BaseValue)),
Result,
BLANK()
)

[Anonymous]

Spoiler

Hi @rajendraongole1 

I tried given DAX but still its working.
I am trying to attached file but it working.
My Drive - Google Drive can try on this link again. i have upload excel file also. and the sceenshot of how its looking in my file.