Forum Discussion
Cumulative total by Month
Hi There,
Hope you are doing well.
I am trying to create on measure where i want Cummulative total of amount by per month.
I have month and year column which is in Text format and i want am not sure how to create cummlative total as i dont have any date available.
Please see below table with sample data . I am expecting the result is which is in "Cummulative Total " coulumn.
Request you to please help me in the same Ashish_Mathur
Thanks,
Ashish
Anonymous You can create a column like this:
YearMonth = [Year] * 100 + [Month]Then use something like this:
5 Replies
- MohammadLoran25
Solution Sage
Hi Anonymous ,
Follow these steps:
1-Create a calculated column in your table as:
YearMonth = CONVERT(CONCATENATE(TABLEofSales[Year],IF(TABLEofSales[Month]<10,CONCATENATE("0",TABLEofSales[Month]),TABLEofSales[Month])),INTEGER)2-Then Create another Calculated Column:YearMonthOrderIndex = RANKX(TABLEofSales,TABLEofSales[YearMonth],TABLEofSales[YearMonth],ASC,DENSE)Then Create the measure below to achieve your desired result:CumulativeSales =
CALCULATE (
SUM ( TABLEofSales[Amount] ),
FILTER (
ALL ( TABLEofSales ),
TABLEofSales[YearMonthOrderIndex] <= MAX ( TABLEofSales[YearMonthOrderIndex] )
)
)If this answer solves your problem, please mark it as an accepted solution so the others would find what they need easier.Regards,LoranHi MohammadLoran25,
Thank you for the solution . I will try this one also as i was in a hurry and tried above and that works for me.
But thank you for the solution
- MohammadLoran25
You're welcome.
- Greg_Deckler
Community Champion
Anonymous You can create a column like this:
YearMonth = [Year] * 100 + [Month]Then use something like this:
Hi Greg_Deckler
Thank you ever so much for your quick response. The solution which you provided works .
Thanks alot.
Cheers!
Ashish
