Deaccumulation of monthly data

[Anonymous]

Hi all,

I have received a data dump from another department within my company. However the data comes in accumulated numbers, and they are unable to provide me with monthly data.

Is there a smart way to deaccumulate data, so i have it month by month and not stacked on top of each other?

Please see an example here below in google sheets:

https://docs.google.com/spreadsheets/d/1yqZIHb5lHHnzyKRMsHedXh9zAZnoHfAwCZhsO3A27jU/edit?usp=sharing...

best regards 

Jakob

[v-lili6-msft]

hi,@Fazniro

    After my research, you add a measure like below:

Measure =
VAR a =
    CALCULATE (
        MAX ( Table1[item A] ),
        FILTER (
            ALL ( Table1[Date] ),
            AND (
                Table1[Date] < MAX ( Table1[Date] ),
                AND (
                    YEAR ( Table1[Date] ) = YEAR ( MAX ( Table1[Date] ) ),
                    MONTH ( Table1[Date] ) = MONTH ( MAX ( Table1[Date] ) )
                )
            )
        )
    )
RETURN
    CALCULATE ( SUM ( Table1[item A] ) - a )

Result:

here is demp, please try it.

https://www.dropbox.com/s/qjeiuawwzo9ap97/Deaccumulation%20of%20monthly%20data.pbix?dl=0

Best Regards,

Lin

[Anonymous]

hi thanks for your reply,

but i still have a problem.

What if data has this type? 

https://docs.google.com/spreadsheets/d/1NSFzIJ-YYMj19L95HRU745ztCm9GmVTRxZKMeCDil7c/edit?usp=sharing

I think the problem might be that the data contains different years, so your script is not working. What should be changed?

best regards

[v-lili6-msft · Accepted solution]

hi,@Fazniro

    After my research, you add a measure like below:

Measure 3 = 
var a=CALCULATE(MAX(Table4[item A]), FILTER (          ALL ( Table4[Date]),         AND (              Table4[Date] < MAX ( Table4[Date] ),                              MONTH ( Table4[Date] ) = MONTH ( MAX (Table4[Date] ) )   )     ) )  return

CALCULATE(SUM(Table4[item A])-a)

here is new pbix, please try it.

https://www.dropbox.com/s/2ilwurzietd7f3h/new%20Deaccumulation%20of%20monthly%20data.pbix?dl=0

Best Regards,

Lin