Add new calculated Column for Monthly Cumulative total based on date & category

[fmouhcine]

Dear Community,

I will need your support please I want to add a calculated column to an existing table where I do have: Date / Country / Volume

Below you can see an example from Excel were I have created manually a new column where I m calculating the Monthly Cumulative volume based on the date (should be for the same month & year) & the country

Thanks in advance for your support

[amitchandak · Accepted solution]

@fmouhcine , Try a new column like

Sumx(Filter(Table, [ctry] = earlier([Ctry]) && [Date] <= earlier([Date]) ) , [Volume])

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s

[fmouhcine]

Hi @amitchandak 

Many thanks for your reply - that works however the main objective is to have the monthly cumulative, meaning the cumulative must be rest at the beginning of each month.

So I have added additional conditions to mutch the month & the year as below:

VAR _Cumul = SUMX(

                FILTER(Table,

                [Ctry] = EARLIER([Ctry]) &&

                [Date] <= EARLIER([Date]) &&

                MONTH([Date]) = Month(EARLIER([date])) &&

                YEAR([Date]) = YEAR(EARLIER([date]))),

                [Volume]

                )

Cheers.

Mouhcine