# Cumulative sum on date AND per item

Good evening,
may i please ask for your help, i am struggling following issue, i would like to create the cumulative sum in an order table, based on date and per item as a calculated column.

thanks a lot, kind regards
Franz

helpfile010320.pbix (60.6 KB)

Hi @Franz,

Here you go. Thanks for providing sample pbix file. I have added date table and following is your required formula to calculate results.

``````Cummulative Total =

VAR currentitem =
VALUES ( 'data'[Item] )
RETURN
IF (
HASONEVALUE ( 'data'[Item] ),
CALCULATE (
SUM ( 'data'[Quantity] ),
FILTER (
ALLSELECTED ( 'data' ),
'data'[Date] <= MAX ( 'data'[Date] )
&& 'data'[Item] = currentitem
)
),
CALCULATE (
SUM ( 'data'[Quantity] ),
FILTER ( ALLSELECTED ( 'data'[Date] ), 'data'[Date] <= MAX ( 'data'[Date] ) )
)
)
``````

2 Likes

Hi Hafiz Sultan,

it works perfect as a measure, please can i have this formula as a â€ścalculated columnâ€ť, i need to evaluate line by in line in my order table against available stock

Franz

Hi @Franz,

Here you go:

``````Desired Result(CC) =
SUMX (
FILTER (
'desired result',
'desired result'[Date] <= EARLIER ( 'desired result'[Date] )
&& 'desired result'[Item] = EARLIER ( 'desired result'[Item] )
),
'desired result'[Quantity]
)
``````
2 Likes

Very nice solution. Plus extra degree of difficulty points for the use of EARLIER - IMO the runaway winner of the Most Confusing DAX Function award.

• Brian
1 Like

Hi @BrianJ,

Thanks, yes indeed, EARLIER is very confusing in the start and I believe one can only understand it after using it for sometime

I just use a VAR to capture the prior row context, and steer clear of it entirely.

• Brian
1 Like

Thank you very much, works perfect

1 Like

Hi @Franz,

Glad that it worked for you. Please mark it as solved. Cheers!

Hi @Franz, I hope that you are having a great experience using the Support Forum so far. Weâ€™ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!