Calculate cumulative backlog for previous period

[Eckhardt_david]

Hello,

I have to create a report which shows the number of planned + the number of shipped orders.
I have this data available in the dataset, but the problem I am encountering is that the planned orders must consist of (planned orders + not shipped orders previous day).

I assume some form of time intelligence is needed to calculate this, but I can't figure it out. 

My idea is that on each ship date the balance of planned minus shipped over the previous period should be looked at and this "backlog" value should be added to the planned orders.

I've taken a small sample of my data for the purpose of this request. It doesn't show here but there's already an cumulative backlog of 104 orders prior to 4/09/2023.

Used tables and fields:

'Outbound_Performance'[Orders_Planned]
'Outbound_Performance'[Orders_Shipped]
'Calendar'[Date]

So the desired result would be to calculate the 'Orders_Planned + Backlog column.

Ship_Date	Orders_Planned	Orders_Planned + Backlog	Orders_Shipped
11/09/2023	5	80	42
8/09/2023	18	138	63
7/09/2023	30	173	53
6/09/2023	37	163	20
5/09/2023	33	132	6
4/09/2023	25	129	30
pre 4/9/2023		104

Any help with this much appreciated!

[ERD · Accepted solution]

Hi @Eckhardt_david , you can try this measure:

Orders_Planned+Backlog =
VAR dt = SELECTEDVALUE ( Outbound_Performance[Ship_Date] )
VAR planned =
    CALCULATE (
        SUM ( Outbound_Performance[Orders_Planned] ),
        Outbound_Performance[Ship_Date] <= dt
    )
VAR shipped =
    CALCULATE (
        SUM ( Outbound_Performance[Orders_Shipped] ),
        Outbound_Performance[Ship_Date] < dt
    )
RETURN
    planned - shipped

[Eckhardt_david]

worked perfectly, thanks!