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

       

       

       

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

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