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

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.

    • PowerBINL's avatar
      PowerBINL
      New Member
      4 years ago

      Thank you.

       

      Are you able to acces the file?


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

       

       

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        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]))