Cumulate prior hour's remaining volume and add to next hour's volume

[HALEYSTODDARD]

Hi!

I'm very new to Power Bi, but I'm hoping to take some suggestions on how to go about this. 

Essentially I’m trying to create a custom column to essentially calculate the previous hour’s remaining qty + next  hour’s allocated qty to get a snapshot of the total volume for each hour, but I can’t figure out the formula. 

So for example, the day starts at midnight (12:00 AM, hour 0) and I have 598 units drop but only picked 29, I need my 1:00 AM new total to be 569 remaining from 12:00 AM + the additional 335 units that were allocated at 1 am which would 904 units total. Then we picked 351 between 1-2am, so for 2am it would take the remaining 553 (904-351 picks) + the 285 that are allocated at 2am and so on and so forth. 

Any tips on how to go about this? 

[johnt75 · Accepted solution]

You could try creating a column like

Running Total =
VAR currentDate = 'Table'[DATE_REF]
VAR currentHour = 'Table'[Hour_ref]
VAR totalAllocated =
    CALCULATE (
        SUM ( 'Table'[Cart Allocated] ),
        ALLEXCEPT ( 'Table', 'Table'[DATE_REF] ),
        'Table'[Hour_ref] <= currentHour
    )
VAR totalPicked =
    CALCULATE (
        SUM ( 'Table'[Cart Picks] ),
        ALLEXCEPT ( 'Table', 'Table'[DATE_REF] ),
        'Table'[Hour_ref] < currentHour
    )
RETURN
    totalAllocated - totalPicked

[HALEYSTODDARD]

that worked perfectly! The only thing I had to change was in the VAR totalPicked the "<" sign to "<=" 

Thanks so much!