Forum Discussion
Add new calculated Column for Monthly Cumulative total based on date & category
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
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
2 Replies
- amitchandak
Super User
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=17820sHi 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
