Monthly moving cumulative sum

[anouk_ppt]

Hi, 

Here is my Power BI file: https://we.tl/t-Z7PKx0RqXe
I'd like to have a table with a monthly cumulative sum for each column (Call Off,  Flux poussé, Flux tiré, Weekly Platinium). Something looking roughly like this for one of the columns:

Month number	Date	Flux poussé	Cumulative Flux poussé
5	05/05/2019	5	5
5	06/05/2019	10	15
5	07/05/2019	16	31
6	06/06/2019	25	25
6	07/06/2019	2	27
6	08/06/2019	10	37

I've seen another post about this (https://community.powerbi.com/t5/Desktop/Monthly-cumulative-sum/td-p/699637) but I wasn't able to adapt it to my problem... Maybe you will !

Thank you in advance,

Anouk

[Mariusz]

Hi @anouk_ppt 

Please see the below.

CALCULATE(
    SUM(YourTable[Flux poussé]),
    FILTER(
        ALLEXCEPT(YourTable, YourTable[Month number]),
        YourTable[Date] <= MAX(YourTable[Date])
    )
)

Regards,
Mariusz

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

[anouk_ppt]

Hi @Mariusz 

First of all, thank you for your prompt response !
With your formula I do get a cumulative sum but it doesn't "reset" every month as you can see below. I would like for it to start at 152 028 on May 2nd then at 186 340 (=152 028 + 34312) on May 3rd and so on.

[Mariusz]

Hi @anouk_ppt 

Try adding Year like below.

CALCULATE(
    SUM(YourTable[Flux poussé]),
    FILTER(
        ALLEXCEPT(YourTable, YourTable[Annee], YourTable[Mois]),
        YourTable[Date] <= MAX(YourTable[Date])
    )
)

Regards,
Mariusz

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

[anouk_ppt]

Hi @Mariusz 

I added the year in the formula just like you showed me but it still doesn't "reset" as you can see on the last line (June 3rd):