Forum Discussion
PowerBINL
4 years agoNew Member
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...
- 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 ) ) - 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
4 years agoNew Member
Hi John,
Thanks. Could you insert the ) at the right position in your initial post? It still provides me with an error
johnt75
Super User
4 years agoEdited now. I'd missed out an entire CALCULATE as well.
- PowerBINL4 years agoNew Member
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?).