Cumulative Total in this DAX is confusing..

[ANNING]

Hi guys,

I happened to read one of the article presented in the SQLBI (https://www.daxpatterns.com/cumulative-total/) and there is one DAX measure in which the author provided is really driving me nuts below as I am very puzzle of how the Sales Amount running total measure is calculated. The following is the DAX and you can also download the PBIX file in the website.

Let's say I filtered the slicers with following values:

My question is how is the Sales Amount RT are derived based on those filter context? I copied the tranformed table for Sales, Date, and Product and did a vlookup to pull out the Category field into the Sales table. And Why the Sales Amount RT is not 123821.44 as shown in the filtered excel below? Basically, how is the above DAX derived? Thanks.

Anning

[v-deddai1-msft]

Hi @ANNING ,

How did you filter the order date column in excel?

Best Regards,

Dedmon Dai

[amitchandak]

@ANNING , You data model is not very clear. My advice would to have date table have formula like

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[date])))

or

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date] <=max(date[date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[GlassShark1]

Help please! This looks like it might solve my problem but it's not clear to me when 'date' is referenced it is only ever referring to the date in the date table or not. When i try it the same total appears in all rows of the date table. Thanks 🙂