Cumulative total by Month

[ashc_cool10]

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

[Greg_Deckler · Accepted solution]

@ashc_cool10 You can create a column like this:

YearMonth = [Year] * 100 + [Month]

Then use something like this:

Better Running Total - Microsoft Power BI Community

[ashc_cool10]

Hi @Greg_Deckler 

Thank you ever so much for your quick response. The solution which you provided works .

Thanks alot.

Cheers!

Ashish

[MohammadLoran25]

Hi @ashc_cool10 ,

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,

Loran

[ashc_cool10]

Hi @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.