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.
Hey guys,
Could you please help me out to get the difference between rows as a calculated column considering a few parameters?
I would need to calculate the difference between sales, having in mind that I need to do it for each Account group separately. I was trying to do it with EARLIER, PREVIOUSMONTH, PARARALEPERIOD but always stopped somewhere in the process.
This is an example of the columns and desired outcome.
Account | Date | Sales | Calculated column |
A | 9/1/2012 | 120 | |
A | 10/1/2012 | 150 | 30 |
A | 11/1/2012 | 150 | 0 |
A | 12/1/2012 | 100 | -50 |
B | 9/1/2012 | 10 | |
B | 10/1/2012 | 20 | 10 |
B | 11/1/2012 | 30 | 10 |
B | 12/1/2012 | 40 | 10 |
Thank you!
Solved! Go to Solution.
Probably something like below. PBIX attached.
Column =
VAR __Previous =
MAXX(
FILTER(
ALL('Table'),
[Account] = EARLIER([Account]) &&
[Date] < EARLIER([Date])
),
[Date]
)
RETURN
[Sales] -
MAXX(
FILTER(
ALL('Table'),
[Account] = EARLIER([Account]) &&
[Date] = __Previous
),
[Sales]
)
Probably something like below. PBIX attached.
Column =
VAR __Previous =
MAXX(
FILTER(
ALL('Table'),
[Account] = EARLIER([Account]) &&
[Date] < EARLIER([Date])
),
[Date]
)
RETURN
[Sales] -
MAXX(
FILTER(
ALL('Table'),
[Account] = EARLIER([Account]) &&
[Date] = __Previous
),
[Sales]
)
Thank you @Greg_Deckler .
Works perfectly in PowerBI. I also use same Excel data model and when I try to use the same approach in Power Pivot it doesn't work there. There it calculates the difference only for the first row?
Do you have any idea why this could be the case?
Tomislav
No idea. I didn't have that issue. See attached xlsx.
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.