Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Dynamic Percentage

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




Need Help

9 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

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

      • rajendraongole1's avatar
        rajendraongole1
        Super User
        1 year ago

        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()
        )

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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.