Cumulative Totals

[JamesGordon]

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?

[az38]

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

[JamesGordon]

Thanks Az38,

I have tried your suggestion but get the following error?

[Anonymous]

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

D

[JamesGordon]

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?

[Anonymous]

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

[JamesGordon]

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?

[Anonymous]

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

D

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[JamesGordon]

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?

[Anonymous]

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

[az38]

Hi @JamesGordon 

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

[JamesGordon]

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?

[Anonymous]

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

Bets
D