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.
Hello,
How to calculate percentage change from prior date (prior record)?
So, in the example below we would want the 9/19 price minus the 9/18 price divided by the 9/18 price. (144.39-144.55)/144.55. then want this to work for every day (besides first day because there was no prior record)
This was very simple in Tableau - just had 'Percent Difference' as an option when displaying values.
Did see a 'Percent Different in 'Quick MEasures' but this seems to be looking for 2 different numeric fields.
Thanks,
Dan
Solved! Go to Solution.
Hi @dancarr22,
Could you try using the formula below to create a new measure to see if it works in your scenario.
Measure = VAR previousDateWithPrice = CALCULATE ( MAX ( Data[Date] ), FILTER ( ALL ( Data ), Data[Price] > 0 && Data[Date] < MAX ( Data[Date] ) ) ) VAR previousPrice = CALCULATE ( SUM ( Data[Price] ), FILTER ( ALL ( Data ), Data[Date] = previousDateWithPrice ) ) RETURN DIVIDE ( SUM ( Data[Price] ) - previousPrice, previousPrice )
Remark: You'll need to replace the bold with your real table name and column name.
Regards
Hi @dancarr22,
Could you try using the formula below to create a new measure to see if it works in your scenario.
Measure = VAR previousDateWithPrice = CALCULATE ( MAX ( Data[Date] ), FILTER ( ALL ( Data ), Data[Price] > 0 && Data[Date] < MAX ( Data[Date] ) ) ) VAR previousPrice = CALCULATE ( SUM ( Data[Price] ), FILTER ( ALL ( Data ), Data[Date] = previousDateWithPrice ) ) RETURN DIVIDE ( SUM ( Data[Price] ) - previousPrice, previousPrice )
Remark: You'll need to replace the bold with your real table name and column name.
Regards
I have a similar problem to which this has been the closest solution I've found so far, but it's still not quite achieving what I need. I'm working with educational data which is parsed out in terms (i.e. 2015SP, 2015SU, 2015FA, 2016SP, 2016SU, etc.). For each term there are over a 1,000 sections for which we need to calculate total full-time equivalent enrollments and then compare the total enrollments from Fall to Fall and Spring to Spring.
How would I adjust your code to achieve this? Here's an example of a matrix I'm trying to create. The values in the measure field are not correct. They should be -4.3% and -4.0%.
Cheers!
Hi,
Share some data and show the expected result - how does only know when the term starts/ends?
Hi,
Try this calculated field formula
=IFERROR([Amount]/CALCULATE(LASTNONBLANK(Data[Price],[Amount]>0),Data[Date]<MAX(Calendar[Date])),BLANK())
Here are my assumptions
If my solution does not work, then share the link from where i can download your file.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |