Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |