cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tbsflm
Helper I
Helper I

Creating a Measure to calculate a percentage change from previous day

I am displaying sales data using slicers in Power BI Desktop, e.g. for a certain product and date range, I see the individual days by date as well as the revenue associated to each day. I now want to create a measure that calculates the difference in revenue between the individual dates based on my selection, e.g. for the second date of my selection, I want to know the percentage difference compared to the first day, for the third day compared to the second day and so on.

When looking for this, I found a similar solution, but in this case, there was an additional column ('Store') that needed to be taken into account and I can't figure out how to change this Measure in order to fit my use case. However, I think the Measure I am looking for should have an easier structure, I just can't find a way to remove all the 'Store' references in the example while preserving the functionality.

Do you have any tips for me on how to proceed?

1 ACCEPTED SOLUTION

Pct Change from prev day =
IF (
    SELECTEDVALUE ( 'Date'[Date] ) > MINX ( ALLSELECTED ( 'Date' ), 'Date'[Date] ),
    VAR currentValue = [Existing measure]
    VAR prevValue =
        CALCULATE ( [Existing measure], PREVIOUSDAY ( 'Date'[Date] ) )
    RETURN
        DIVIDE ( currentValue - prevValue, prevValue )
)

This will return blank on the first day of the selection

View solution in original post

6 REPLIES 6
johnt75
Super User
Super User

You can try

Pct Change from prev day =
var currentValue = [Existing measure]
var prevValue = CALCULATE( [Existing measure], PREVIOUSDAY('Date'[Date]))
return DIVIDE( currentValue - prevValue, prevValue)

Thank you for this suggestion!

This might be a stupid question, but what would I use instead of "[Existing measure]"?

The measure which you have which shows revenue, which you are currently using in your visual. If you haven't created one, and are just using the implicit measure created by Power BI if you dropped a column onto the visual, you'll need to create one something like

Revenue Measure = SUM('Table'[Revenue])

Ah, lovely, it works.

This might be a small detail, but in the current version, the measure also returns a percentage value for the first day of my range, probably taking into account the day before, which is not displayed. Can I somehow take this into account, e.g. the measure only works for the displayed days and therefore 'ignores' the first day?

Pct Change from prev day =
IF (
    SELECTEDVALUE ( 'Date'[Date] ) > MINX ( ALLSELECTED ( 'Date' ), 'Date'[Date] ),
    VAR currentValue = [Existing measure]
    VAR prevValue =
        CALCULATE ( [Existing measure], PREVIOUSDAY ( 'Date'[Date] ) )
    RETURN
        DIVIDE ( currentValue - prevValue, prevValue )
)

This will return blank on the first day of the selection

Perfect, thank you so much for your detailed help!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors