Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Dynamic percentage

Hello Everyone

I have a table with columns for Date, Code, and Price. I want to calculate the percentage change in price between two selected dates for any given code. For example, if I select dates from 2022 to 2024 in a slicer, the percentage change should be calculated from the base price in 2022. The percentage change should be 0% at the start of 2022, and from that point onward, the change should be dynamically calculated.

If I select a different date range, the calculation should use the base price from the new starting date. The solution should be dynamic to accommodate any selected dates.


Click on the link to get sample data = Sample Data.xlsx - Google Sheets

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    Please create a new calendar table and a measure:

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

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

    when I am applying with same logic with my original data its not working.
    I am getting blank line chart.


    I tried both with creating new date table and keeping the same date table with original data also but its not working.