SAP Order Backlog over time until delivery date

[Anonymous]

Hi everybody,

I am currently facing the challenge to show how my order backlog develops over time until the expected delivery date.

I have SAP sales data where we have order intake postings as well as reduction postings when there was a sale for the order.

I already have a measure that shows the order intake, one for the reduction postings and another one that shows the order backlog over time. However it continues until forever but I would want it to only show the order backlog until the expected delivery date (which is the posting date in the order entry row) for every order.

How can I achieve this?

My data looks like this.

Order	Posting	Intake Date	Posting Date	Amount
12345	Order Entry	05.11.2022	24.01.2023	1000
12345	Reduction Posting	05.11.2022	10.12.2022	-700

In this case I would want to see an order backlog of 1000 from 05.11.2022 - 09.12.2022, then order backlog of 300 from 10.12.2022 - 23.01.2023. My measure already does most of it, but it shows the 300 until forever, not only until 23.01.2023.

Somehow I need to tell the function to do this calculation for every order and then stop at the expected delivery date. But I don't know how...

This is my code

Order Bklog = 
CALCULATE(
    CALCULATE(
        [Order Int] + [Reduction Postings],
        'Fact Order'[Source] <> "HIST"),
    FILTER(
        ALL('Dim Date'),
        AND(
            'Dim Date'[Date] <= MAX('Dim Date'[Date]),
            NOT(ISBLANK('Dim Date'[Date])))))

Thanks in advance!

Janina