Cummulative total by month by product

[terdudov]

I have a request for help with my case. I have tried various solutions from here, but my case seems somehow specific.

I have this kind of data in a table:

Date	Analytical account	Account value
04.V.22	165000	3 000
04.V.22	165000	50
04.V.22	165000	10
01.I.22	251000	40
24.II.22	251000	50
04.V.22	251000	40 000
04.V.22	251000	200
18.II.22	432000	20
08.V.22	432000	1 000

I need to create a pivot/graph with cummulative totals by Month AND Analytical account. The result should show:

Analytical account	Cummulative value
165000	3 060
V	3 060
251000	40 290
I	40
II	90
V	40 290
432000	1 020
II	20
V	1 020

Thank you in advance for help!

[v-yinliw-msft · Accepted solution]

Hi @terdudov ,

You can try this method.

Cummulative Value = 
CALCULATE (
    SUM ( 'Table'[Account value] ),
    FILTER (
        ALL ( 'Table' ),
        [Analytical account] = MAX ( 'Table'[Analytical account] )
            && [Month] <= MAX ( 'Table'[Month] )
    )
)

Is this the result you expect?

Hope this helps you.

Best Regards,

Community Support Team _Yinliw

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

[VahidDM]

Hi    @terdudov 

If you need somthing like :

Then download the sample file and check the formulas:
1- Add a column for month: 

Month = Format('Table'[Date],"mmm")

2- Then use this measure:
Sum ACC Value = CALCULATE ( SUM ( 'Table'[Account value] ), REMOVEFILTERS ( 'Table'[Month] ), KEEPFILTERS ( 'Table'[Date] <= MAX ( 'Table'[Date] ) ) )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[terdudov2]

Hello @VahidDM 

Thank you a lot, your solution gave me my result. Only I have one issue. What if  want to have all months from 1-12 in the table? Then it shows me no values for example for Analytical account 251000, month 3. However I would want it to show month-to-date value = 90 (e.g. value from moths 1+2). Is there a solution for this?

Thank you!

[VahidDM]

You can add IF statement and add those rules to it, that will work for your condition

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube