cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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!:
Mastering Power BI 2nd Edition

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!:
Mastering Power BI 2nd Edition

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors