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 have a table like so:
ID| Date| Value
15123 | 21.7.2010 | 121 |
15123 | 28.7.2010 | 211 |
15123 | 4.8.2010 | 299 |
15123 | 11.8.2010 | 386 |
15123 | 18.8.2010 | 463 |
15123 | 25.8.2010 | 588 |
15123 | 1.9.2010 | 645 |
15123 | 8.9.2010 | 704 |
15123 | 15.9.2010 | 766 |
15999 | 22.9.2010 | 802 |
15999 | 29.9.2010 | 946 |
15999 | 6.10.2010 | 991 |
15999 | 13.10.2010 | 1006 |
15999 | 20.10.2010 | 1021 |
15123 | 13.7.2011 | 2 |
15123 | 20.7.2011 | 8 |
15123 | 27.7.2011 | 27 |
15123 | 3.8.2011 | 41 |
15123 | 10.8.2011 | 110 |
15123 | 17.8.2011 | 139 |
15123 | 24.8.2011 | 219 |
15123 | 31.8.2011 | 275 |
15123 | 7.9.2011 | 312 |
15123 | 14.9.2011 | 373 |
15123 | 21.9.2011 | 404 |
15123 | 28.9.2011 | 428 |
15999 | 5.10.2011 | 451 |
15999 | 12.10.2011 | 473 |
15999 | 19.10.2011 | 476 |
The values from the source are already cumulative.
My desired output is this:
ID | Date| Value| Decumulative values
15123 | 21.7.2010 | 121 | 121 |
15123 | 28.7.2010 | 211 | 90 |
15123 | 4.8.2010 | 299 | 88 |
15123 | 11.8.2010 | 386 | 87 |
15123 | 18.8.2010 | 463 | 77 |
15123 | 25.8.2010 | 588 | 125 |
15123 | 1.9.2010 | 645 | 57 |
15123 | 8.9.2010 | 704 | 59 |
15123 | 15.9.2010 | 766 | 62 |
15123 | 22.9.2010 | 802 | 36 |
15999 | 29.9.2010 | 946 | 946 |
15999 | 6.10.2010 | 991 | 45 |
15999 | 13.10.2010 | 1006 | 15 |
15999 | 20.10.2010 | 1021 | 15 |
15123 | 13.7.2011 | 2 | 2 |
15123 | 20.7.2011 | 8 | 6 |
15123 | 27.7.2011 | 27 | 19 |
15123 | 3.8.2011 | 41 | 14 |
15123 | 10.8.2011 | 110 | 69 |
15123 | 17.8.2011 | 139 | 29 |
15123 | 24.8.2011 | 219 | 80 |
15123 | 31.8.2011 | 275 | 56 |
15123 | 7.9.2011 | 312 | 37 |
15123 | 14.9.2011 | 373 | 61 |
15123 | 21.9.2011 | 404 | 31 |
15123 | 28.9.2011 | 428 | 24 |
15999 | 5.10.2011 | 451 | 451 |
15999 | 12.10.2011 | 473 | 22 |
15999 | 19.10.2011 | 476 | 3 |
So basically - De-cumulate the Cumulative values within each ID and YEAR
I'm trying to use EARLIER() but I'm kinda lost here
Appreciate your help
Solved! Go to Solution.
Ok I just solved this one. Maybe there's a better way
De-cumulative value = VAR _predate = CALCULATE ( MAX ( 'Table'[Date] ); FILTER ( 'Table'; 'Table'[Date] < EARLIER ( 'Table'[Date] ) ); 'Table'[ID] = EARLIER ( 'Table'[ID] ); YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) ) ) VAR _prevalue = CALCULATE ( SUM ( 'Table'[CumulativeValue] ); FILTER ( 'Table'; 'Table'[Date] = _predate ); 'Table'[ID] = EARLIER ( 'Table'[ID] ) ) RETURN 'Table'[CumulativeValue] - _prevalue
Hi @Yggdrasill ,
I have another solution that needs an index column. Then use EARLIER() function to get previous row value.
ValueDiff = Table1[Value] - CALCULATE ( FIRSTNONBLANK ( Table1[Value], 1 ), FILTER ( Table1, Table1[Index] = EARLIER ( Table1[Index] ) - 1 && Table1[ID] = EARLIER ( Table1[ID] ) ) )
You can try my method and consider which one is more acceptable.
Ok I just solved this one. Maybe there's a better way
De-cumulative value = VAR _predate = CALCULATE ( MAX ( 'Table'[Date] ); FILTER ( 'Table'; 'Table'[Date] < EARLIER ( 'Table'[Date] ) ); 'Table'[ID] = EARLIER ( 'Table'[ID] ); YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) ) ) VAR _prevalue = CALCULATE ( SUM ( 'Table'[CumulativeValue] ); FILTER ( 'Table'; 'Table'[Date] = _predate ); 'Table'[ID] = EARLIER ( 'Table'[ID] ) ) RETURN 'Table'[CumulativeValue] - _prevalue
Hi @Yggdrasill ,
I have another solution that needs an index column. Then use EARLIER() function to get previous row value.
ValueDiff = Table1[Value] - CALCULATE ( FIRSTNONBLANK ( Table1[Value], 1 ), FILTER ( Table1, Table1[Index] = EARLIER ( Table1[Index] ) - 1 && Table1[ID] = EARLIER ( Table1[ID] ) ) )
You can try my method and consider which one is more acceptable.
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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |