cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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.

Thanks,

Ashish

1 ACCEPTED SOLUTION
Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
5 REPLIES 5
Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper III

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

Thanks alot.

Cheers!

Ashish

Super User

Hi @ashc_cool10 ,

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
Helper III

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

Super User

You're welcome.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.