Cumulated sum by date and by group, when simalar dates appear

[Rose__123]

Hi

I need to calculate the cumulative sum of inventory per item in order to make a waterfall chart to display the development of items in the warehouse. The image below shows my data. The problem arises because I have multiple posts with the same date, which becomes a problem when I try to make the waterfall chart. 

How can I get past this?

Code to calculate the cumulated sum:

Cumul =

CALCULATE( SUM(Inventory_hist[Inventory_change]),

FILTER(

ALLEXCEPT( Inventory_hist, Inventory_hist[No]),

Inventory_hist[Data_change]<=EARLIER( Inventory_hist[Data_change])

))

\Katrine

[v-xiaotang · Accepted solution]

Hi @Rose__123 

try this measure

Measure = 
    var _total=CALCULATE(SUM(Inventory_hist[Inventory_change]),FILTER(ALL(Inventory_hist),Inventory_hist[Data_change]<=MIN(Inventory_hist[Data_change])))
    var _minNo=CALCULATE(MIN(Inventory_hist[No]),ALLEXCEPT(Inventory_hist,Inventory_hist[Data_change]))
return 
    IF(MIN(Inventory_hist[No])=_minNo,_total)

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[v-xiaotang]

Hi @Rose__123 

sample file attached below

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Waterfall charts do not work well if you have multiple data rows per x axis value. Try setting the aggregation for "cumul"  to Average, or max.

[lbendlin]

what exactly is the problem?  What should be the expected outcome?

[Rose__123]

@lbendlin, the problem arises e.g. on the 28. Juli, where I have five sales of 24, 48, 48, 48, and 60 items. When I calculate the cumulated sum by date (and item), then the number for all five rows will be the sum of those five sales (plus the sum of the earlier sales=276). And when I make my waterfall chart on behalf of those data, it gets the number 276 five times instead of one time. 
I know that I could make a new table that removed the repeated lines, but I would like to avoid that. 

Does it makes sense, or should I make an example?