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 cha...
Need Help
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()
      )

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