Calculating a Cumulative Sum in Power BI

[Anonymous]

Hi everyone,

I need some help putting together a calculated column or measure for a Net Requirements report in Power BI.  

Here is a sample of my data:

What I need is a a cumulative sum for the Requirement Quantity for each line.  The first line's value should be 615.00, then 565.00, then 585.00 and so on.  There are changes by the 'Number' field and by the 'Requirement Date' field.  Anyone have any idea how to create this?

Thanks!

[gckcmc]

A running total of inventory on hand is effectively what I interpret you to mean you need.  In this table, you're describing things by date.  Without a time stamp, you could have several ways this gets interpreted.  for example:

615

-50

+20

+10

+10

+10

which is effectively in the order you have the sample listed.  However, you could just as easily have:

615

+20

+10

+10

-50

as all the +/- are happening on the same day.

So since the query is going to really not have any row level associations, you have to determine if your input data is in sequential order on purpose, and if that is the case, then I would build a solution that adds in a column which is simply a sequential number counter based on the row.  This is done in lots of cases as an "ID" type column.  Then when you build out the chart, you can have the matrix, or graph or whatever, sort by the "ID" column and you preserve order of the data you loaded.

If order of data isn't from what you loaded, then I would go by date column (adding time stamp if you want your inbound to get added to your running inventory total before outbound gets deducted, etc.)

hope this helps.

[Greg_Deckler]

I would recommend implementing an Index column in Power Query. The solution should then be trivial for either a column or measure formula.

[Anonymous]

Thanks for the response - I've created the Index Column in Power Query, is there a function I can use for the Cumulative sum in Power Query or should it be done as a calculated column in DAX?

[gckcmc]

I guess it depends on what you're trying to achieve.  For example if you want your visualization to be a matrix or chart, then you can simply use the index to arrange the order of the inventory column.  

A calculated cumulative (measure/function) I'm not sure makes sense given your scenario...i.e. when do you decide to "stop" the cumulative count?  are you going to specify and index?  a date? or no stop at all, and just a sum of the entire inventory running total, which gets you a net sum at the end of your data?