topic Re: Cumulative Total in DAX Commands and Tips

Cumulative Total

JamesGordon — Thu, 27 Feb 2020 09:20:34 GMT

Hi,

I am really struggling to get my montly sales to cumulative total. I have tried numerous DAX and varations on date tables but still cannot get the data to count cumulatively.

In order to calculate my YTD total sales i have created a DAX as below to filter the relevant rows.

Total All Sales YTD = CALCULATE(SUM('Sheet (2)'[Total Sell]), FILTER('Sheet (2)', 'Sheet (2)'[Status] = "S"), filter('Sheet (2)', 'Sheet (2)'[Total Cost]>100))

Could this be affecting my results?

Below is how the data looks if i put it into a table

Power BI appears to have already created a date table from my "Invoice Date" column.

IS there a simple DAX that can count each months sales to create a cumulative total againt month?

Thanks in advance!

Re: Cumulative Total

Anonymous — Thu, 27 Feb 2020 11:06:28 GMT

Refer this

https://community.powerbi.com/t5/Desktop/Cumulative-Total/m-p/43115

https://community.powerbi.com/t5/Desktop/Cumulative-Total/m-p/857660

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Re: Cumulative Total

JamesGordon — Thu, 27 Feb 2020 11:21:51 GMT

Thanks Anonymous

I have seen those posts and this is the result i get.

A side from the values being wrong (because i need to add some filters) they are still not adding cumulatively?

Re: Cumulative Total

Anonymous — Thu, 27 Feb 2020 12:14:37 GMT

What does EARLIER do in the measure?

Best
D

Re: Cumulative Total

Anonymous — Thu, 27 Feb 2020 12:17:49 GMT

To be able to create YTD measures you need to have a calendar in your model. Having dates in your fact tables and operating on them directly is a sure way to fail.

Please follow the guidelines of dimensional design (star schema) and you'll be writing simple and correct DAX in no time.

Best
D