Calculate a cumulative year to year

[Tiff]

Hello everyone 🙂 

I have a Table called 'ESSAIS' with various columns. 

One of them is the quantity month by month. I have a column for cumulative quantity. 

I did like this to have my cumulative column : 

Cumule = CALCULATE(SUM(ESSAIS[QtyMonth]);ALL(ESSAIS);ESSAIS[Date]<=EARLIER(ESSAIS[Date])) 

I've data on four years, and I'd like to reset my cumulative column on each january... 

Is it possible on Power BI ? I haven't found it on the web... Really disturbing ! 

I tried, with a new column, to test the month. And the month of January, put 0 in the cumulative Column. It's okay, but doesn't reset the SUM... And the month on February is added to the precedent sum... 

Thank you very much for your help ! 

[Tiff · Accepted solution]

Found what I wanted ! 

If it can help any of you, the formula : 

ECC REMISZERO = VAR myDate = ESSAIS[Date]
RETURN
CALCULATE(
SUM(ESSAIS[QtyMois]);
FILTER(
ESSAIS;
ESSAIS[Date]<=myDate
&& YEAR(ESSAIS[Date])=YEAR(myDate)))

It resets the sum every January ! 😄

[Jo5hua22]

Hi @Tiff 

Do you know how to use this in measure?

[v-yulgu-msft]

Hi @Tiff,

You may need this formula:

Cumule =
CALCULATE (
    SUM ( ESSAIS[QtyMonth] );
    FILTER (
        ALLEXCEPT ( ESSAIS; ESSAIS[Date].YEAR );
        ESSAIS[Date] <= EARLIER ( ESSAIS[Date] )
    )
)

Best regards,

Yuliana Gu

[Tiff]

Hi Yuliana, 

Thank you for your answer ! 

However, it doesn't reset every January... It is a cumulative sum... On my whole data. 
Do you know how to reset this SUM every January ? (I have data every day between the first of January 2018 and the first of January 2021. 

I would like my SUM to be at zero every month of January.... 

I admit I'm totally lost on this one... 😮 

Thanks for your help !