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]))
johnt75
Super User
4 years agoNot 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
This seems to be the solution. Thank you John! I could not have solved this