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

  • PowerBINL's avatar
    PowerBINL
    New Member
    4 years ago

    Hi John,

     

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

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

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

      • PowerBINL's avatar
        PowerBINL
        New Member
        4 years ago

        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?).