Skip to main content
cancel
Showing results for 
Search instead 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

Reply
ashc_cool10
Helper III
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.

 

cummulative total.JPG

 

Request you to please help me in the same @Ashish_Mathur 

 

Thanks,

Ashish

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler 

 

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

Thanks alot.

 

Cheers!

Ashish

MohammadLoran25
Super User
Super User

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

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

You're welcome.

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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