cancel
Showing results for
Search instead for
Did you mean:
Member

Deaccumulation of monthly data

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

Re: Deaccumulation of monthly data

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team

Re: Deaccumulation of monthly data

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

Re: Deaccumulation of monthly data

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

Community Support Team

Re: Deaccumulation of monthly data

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.