Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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)
Anonymous
Not applicable

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])
Anonymous
Not applicable

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

Anonymous
Not applicable

Perfect, thank you so much for your detailed help!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.