cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fazniro Member
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
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)

5.PNG

 

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
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:

7.PNG

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.
Fazniro Member
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
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)

5.PNG

 

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.