Calculating cumulative backlog with multiple instances per order

[PowerBINL]

Hi all,

I am trying to calculate the cumulative backlog based on the order ID. Orders are both planned and fulfilled, although fulfilment (production) of the order does not have to be on the same day. 

Two fact tables basically exist in my model:

- Planning table with order ID, order qty, and deadline 

- Production table with order ID, Qty produced and production date.

I want to calculate the total backlog of all the orders that were not fulfilled in time. Orders that are produced before the deadline are also fine and are not penalized or something. Backlog is calculated on a day basis

To illustrate: 

Planning: 

• Order A1, Qty = 100, Deadline = 20-03-2022
• Order B2, Qty = 30, Deadline = 21-03-2022

Production: 

• Order A1, Qty = 20, Date = 19-03-2022 (orders can be produced before deadline)
• Order A1, Qty = 50, Date = 20-03-2022
• Order A1, Qty = 10, Date = 21-03-2022
• Order A1, Qty = 20, Date = 22-03-2022
• Order B1, Qty = 10, Date = 21-03-2022
• Order B1, Qty = 10, Date = 21-03-2022
• Order B1, Qty = 10, Date = 22-03-2022

Which leads to the following, when one would build in Excel:

I have multiple (combinations) of proposed solutions from other questions, but have not managed to create the figure in PowerBI. I sincerely hope you guys can help! Looking forward to a respons.

[johnt75 · Accepted solution]

You can create a measure like

Backlog =
VAR currentDate =
    MAX ( 'Date'[Date] )
RETURN
    SUMX (
        'Planning',
        IF (
            SELECTEDVALUE ( 'Planning'[Deadline] ) > currentDate,
            0,
            VAR currentQty =
                SELECTEDVALUE ( 'Planning'[Qty] )
            VAR totalProduced =
                CALCULATE (
                    SUMX ( RELATEDTABLE ( 'Production' ), 'Production'[Qty] ),
                    REMOVEFILTERS ( 'Date' ),
                    'Production'[Date] <= currentDate
                )
            RETURN
                currentQty - totalProduced
        )
    )

[johnt75 · Accepted solution]

You can create a measure like

Backlog =
VAR currentDate =
    MAX ( 'Date'[Date] )
RETURN
    SUMX (
        'Planning',
        IF (
            SELECTEDVALUE ( 'Planning'[Deadline] ) > currentDate,
            0,
            VAR currentQty =
                SELECTEDVALUE ( 'Planning'[Qty] )
            VAR totalProduced =
                CALCULATE (
                    SUMX ( RELATEDTABLE ( 'Production' ), 'Production'[Qty] ),
                    REMOVEFILTERS ( 'Date' ),
                    'Production'[Date] <= currentDate
                )
            RETURN
                currentQty - totalProduced
        )
    )

[PowerBINL]

Hi John,

Thank you for the quick response. 

I have three follow-up questions based on your proposals:

1. Does it assumes a third date table with relationsship as below? Or should it relate based on orderID

2. In which table should the measure be created?

3. The SUMX function you wrote has three arguments, which I am not able to recreate. How to deal with it?

Looking forward to your reply.

[johnt75]

My solution assumes that there is no relationship from the Date table to either of the 2 tables, and a one-to-many relationship on orderID between planning and production.

It doesn't make any difference which table the measure is created on, it could go on either. Sometimes people like to have a separate table just for measures.

There should only be 2 arguments to the SUMX, I missed a closing ) after SELECTEDVALUE(Planning[Deadline])

[PowerBINL]

Hi John,

Thanks. Could you insert the ) at the right position in your initial post? It still provides me with an error

[johnt75]

Edited now. I'd missed out an entire CALCULATE as well.

[PowerBINL]

Thanks. It provides me with the figure below. It does work, but does not gives the desired/expected result. Is there any way I can share the Pbix file, maybe it makes it easier (WeTransfer?).

[johnt75]

Yes, you can share the file. Dropbox, Google Drive, OneDrive, whatever.

[PowerBINL]

Thank you.

Are you able to acces the file?

It does seem to work for individual orders b.t.w.!

[johnt75 · Accepted solution]

Not sure why its not working at the aggregate level, but I was able to get a working aggregate with

Total Backlog = IF( ISINSCOPE( 'Planning'[Order ID]), [Backlog],
var summaryTable = ADDCOLUMNS( SUMMARIZE( 'Planning',Planning[Order ID]), "@val", [Backlog])
return SUMX( summaryTable, [@val]))

[PowerBINL]

This seems to be the solution. Thank you John! I could not have solved this