Un-cumulate Cumulative Values

[BjoernSchaefer]

Hello Everybody,

I'm dealing with a little issue right now. I have a datamodel containing a Fact-Table with several Dimension-Tables. The Fact-Table contains transactions for Accounts over the last two years. The Date ist always the first of the month. The Problem is: The database only delivers cumulative values by month and not the actual values for each month. Now i need to "un-cumulate" the values for the months. Has anybody got an idea how to solve this? See some example data below and thank you very much for every response i'm gettin'.

Kind regards

BjoernSchaefer

[PattemManohar]

@BjoernSchaefer Could you please post the sample data (Copiable format) and expected output which will help to understand the issue bit more detail.

[BjoernSchaefer]

AccountDateValue CumulativeDescriptionExpected OutcomeDescription2

1753	01.01.2017	157	<- These are the running totals by month	157
1753	01.01.2017	152		152
1753	01.01.2017	177		177
1753	01.01.2017	120		120
1753	01.01.2017	138		138
1753	01.01.2017	129		129
1753	01.01.2017	141		141
1753	01.01.2017	118		118
1753	01.01.2017	175		175
1446	01.01.2017	137		137
1446	01.01.2017	181		181
1446	01.01.2017	165		165
1446	01.01.2017	114		114
1446	01.01.2017	165		165
1446	01.01.2017	180		180
1446	01.01.2017	115		115
1446	01.01.2017	128		128
1446	01.01.2017	181		181
1446	01.01.2017	182		182
1446	01.01.2017	190		190
1753	02.01.2017	301		144	<- This is the value i need. It's the difference to January 1st.
1753	02.01.2017	273		121
1753	02.01.2017	367		190
1753	02.01.2017	270		150
1753	02.01.2017	328		190
1753	02.01.2017	267		138
1753	02.01.2017	307		166
1753	02.01.2017	229		111
1753	02.01.2017	364		189
1446	02.01.2017	297		160
1446	02.01.2017	360		179
1446	02.01.2017	296		131
1446	02.01.2017	271		157
1446	02.01.2017	269		104
1446	02.01.2017	302		122
1446	02.01.2017	289		174
1446	02.01.2017	326		198
1446	02.01.2017	371		190
1446	02.01.2017	336		154
1446	02.01.2017	304		114

[PattemManohar]

@BjoernSchaefer Just to confirm, is this what you are expecting as output in a new table

1753 01.01.2017 157
1753 02.01.2017 144

Or you want to display in new column as mentioned above

[BjoernSchaefer]

@PattemManohar, this is correct. I need an extra column. But i'm not able to figure out how to do that. Thank you very much for your responses.

[PattemManohar · Accepted solution]

@BjoernSchaefer Here is the steps I've followed to solve this.

Step 1 : Add an Index Column (start from 1) in "Power Query"

Step 2 : Add an additional column using "New Column" as below

RankAccount = RANKX(FILTER(Test04UnCumm,Test04UnCumm[Account]=EARLIER(Test04UnCumm[Account]) && Test04UnCumm[Date] = EARLIER(Test04UnCumm[Date])),Test04UnCumm[Index],,ASC)  

Step 3 : Finally, here is the magical column we are looking for

UnCummVal = 
VAR _PrevCummVal = LOOKUPVALUE(Test04UnCumm[CummVal],Test04UnCumm[Account],Test04UnCumm[Account],Test04UnCumm[Date],Test04UnCumm[Date]-1,Test04UnCumm[RankAccount],Test04UnCumm[RankAccount])
RETURN IF(_PrevCummVal=BLANK(),Test04UnCumm[CummVal],Test04UnCumm[CummVal] - _PrevCummVal) 

The output will be as below :

Uhhh !! Nice one 🙂

[BjoernSchaefer]

@PattemManohar, this is it. I already tried that one with the lookupvalue and the index but hadn't got the clue to use a rank. That was the missing link. Thank you so very much. I really appreciate it.