Forum Discussion

PowerBINL's avatar
PowerBINL
New Member
4 years ago
Solved

Calculating cumulative backlog with multiple instances per order

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.




Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    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's avatar
    johnt75
    4 years ago

    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]))

10 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      PowerBINL
      New Member
      4 years ago

      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's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago

        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])