topic Re: Cumulative Totals in DAX Commands and Tips

Cumulative Totals

JamesGordon — Mon, 24 Feb 2020 15:32:45 GMT

Hi,

I am struggling to calculate cumulative totals. Hopefully someone can help!

I have a measure that i have created to calculate the total value of sales YTD (fromt Oct 2019 to today) - this is the measure i am basing my cumulative totals off if possible? (FYI this measure works for calculating YTD sales value)

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))

I am looking to use this measure as the basis for my cumulative totals to ensure it uses the correct values. I have tried to add the ALL filter as per previous

posts to start the cumulative values but having no luck.

Here is the measure i have tried to create but get the below error:

Cumulative test = CALCULATE(SUM('Sheet (2)'[Total Sell]), FILTER('Sheet (2)', 'Sheet (2)'[Status] = "S"), filter('Sheet (2)', 'Sheet (2)'[Total Cost]>100),all('Sheet (2)'[Inv date].[Date])<=MAX('Sheet (2)'[Inv date].[Date]))

Am i on the right track or completely off course?

Re: Cumulative Totals

az38 — Mon, 24 Feb 2020 15:45:59 GMT

Hi JamesGordon

i think you can use much more simple version

Cumulative test = CALCULATE(SUM('Sheet (2)'[Total Sell]), 'Sheet (2)'[Status] = "S", 'Sheet (2)'[Total Cost]>100 , 'Sheet (2)'[Inv date]<=MAX('Sheet (2)'[Inv date]) )

Re: Cumulative Totals

JamesGordon — Mon, 24 Feb 2020 15:50:01 GMT

Thanks Az38,

I have tried your suggestion but get the following error?

Re: Cumulative Totals

az38 — Mon, 24 Feb 2020 15:56:24 GMT

Hi JamesGordon

you did not use my suggestion, i see a lot of filters in your statement

Re: Cumulative Totals

Anonymous — Mon, 24 Feb 2020 16:05:03 GMT

Cumulative test = var __maxDate = MAX ( 'Sheet (2)'[Inv date] ) var __minCost = 100 var __status = "S" return CALCULATE ( SUM ( 'Sheet (2)'[Total Sell] ), keepfilters('Sheet (2)'[Status] = __status), keepfilters('Sheet (2)'[Total Cost] > __minCost), 'Sheet (2)'[Inv date] <= __maxDate )

Copy this as is. No changes.

Best

Re: Cumulative Totals

JamesGordon — Mon, 24 Feb 2020 16:03:32 GMT

Hi Az38,

Apolagies, I have now removed the filters and directly copied and pasted your suggestion but still getting the same error in regards the MAX function

"A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Is there another function I should be using instead?

Re: Cumulative Totals

Anonymous — Mon, 24 Feb 2020 16:06:03 GMT

Mate, MAX can't be used in this context. Copy and paste my function.

Bets
D

Re: Cumulative Totals

JamesGordon — Mon, 24 Feb 2020 16:19:46 GMT

Hi D,

Thank you for your DAX - that is working with no errors.

However my graph is still presenting the data on a Monthly basis and not accumulating? I am using the Inv Date as the X-axis and your DAX as the measure - or am i missing a step?

Re: Cumulative Totals

Anonymous — Mon, 24 Feb 2020 16:44:34 GMT

What's in [Inv Date]? Do you have dates in there? I mean, not text looking as a date but real dates? Date data type?

Best
D

Re: Cumulative Totals

JamesGordon — Mon, 24 Feb 2020 16:46:54 GMT

I would say i have text that looks like dates.

By your comment i am guessing i need to create a date data type? If so how would you recommend the best way forward?

Re: Cumulative Totals

Anonymous — Mon, 24 Feb 2020 16:47:10 GMT

By the way... Why do I see the names of months? Are these months from the same year or they are just months from all the years?

Best
D

Re: Cumulative Totals

Anonymous — Mon, 24 Feb 2020 16:51:02 GMT

Please remember this well: Each and every model needs to have a proper Date table. A proper date table in DAX is a must. Please google for 'date table in power bi' or 'date table in dax.' Try to read an article about how to handle dates in DAX on www.sqlbi.com or powerpivotpro.com. Your model does not have a proper date table, hence your problems.

Best
D

Re: Cumulative Totals

JamesGordon — Mon, 24 Feb 2020 16:59:13 GMT

Power Bi appears to have automatically recognised my "Inv Dates" as dates - screenshot below

However if i use the "Inv Date" as the X-axis with your DAX as my measure it still produces the graph as per my previous post and doesnt accumulate the values?

Re: Cumulative Totals

Anonymous — Mon, 24 Feb 2020 17:01:21 GMT

You should never, ever, ever rely on automatic dates in Power BI. Simple as that. If you want to know why, you can do to www.sqlbi.com and find the articles by The Italians that will explain to you in detail why this reliance is a bad idea.

Best
D

Re: Cumulative Totals

Anonymous — Mon, 24 Feb 2020 17:03:49 GMT

By the way... Can you please put the file on a shared drive (or part of it) so that I can take a look at why exactly the formula does not work? If I can't investigate this, then you have to just follow my advice. But you'd better create a proper Date table anyway. Otherwise, you'll be suffering. I've warned you.

Best
D

Re: Cumulative Totals

Anonymous — Mon, 24 Feb 2020 17:08:24 GMT

I think I know why it does not work... This is again the automatic dates feature rearing its ugly head. Please replace [Inv Date] with [Inv Date].[Date]. But be warned - this will bite you many a time if you don't do what I've said in my previous posts.

best
D

Re: Cumulative Totals

Anonymous — Mon, 24 Feb 2020 17:10:20 GMT

Please try this:

Cumulative test = var __maxDate = MAX ( 'Sheet (2)'[Inv date].[Date] ) var __minCost = 100 var __status = "S" return CALCULATE ( SUM ( 'Sheet (2)'[Total Sell] ), KEEPFILTERS('Sheet (2)'[Status] = __status), KEEPFILTERS('Sheet (2)'[Total Cost] > __minCost), 'Sheet (2)'[Inv date].[Date] <= __maxDate )

Warning: DO NOT EVER RELY ON THE AUTOMATIC DATES FEATURE IN POWER BI. Unless you want to suffer...

Best