PowerBI calculate overflow backlog in measure

Question

My data is as follows:

Factory ID	Week	CAPACITY	Request
1	21	1000	500
1	22	1000	1200
1	23	1500	1600
1	24	1500	1100
2	21	1000	500
2	22	2000	1900
2	23	2000	1000
2	24	2000	2500
3	21	1000	200
3	22	1000	900
3	23	1000	1300
3	24	1000	800

I want to calculate backlog in a measure or any other way to have backlog be dynamic based on the factories I select. Backlog is calculated as follows:

Backlog = Capacity - (Request + Previous week backlog); where we have backlog when requests + pre. week backlog exceeds capacity or else it is 0. I cannot move capacity from future weeks, so the backlog would always accumulate going forward

Eg. If I select Factory 1, my backlog should look as follows:

Factory Selected: 1

Week	Backlog
21	0
22	-200
23	-300
24	-100

Factory Selected: 1,2

Week	Backlog
21	0
22	-100
23	0
24	-400

Factory Selected: 1,3

Week	Backlog
21	0
22	-100
23	-500
24	0

I have been trying to find a solution since the last 2 days. Let me know if you need any additional details. Any help will be greatly appreciated.

@AntrikshSharma For factory 1 Week 1 Backlog = 1000 - 500 - 0(backlog from prev week)= 0 Week 2 Backlog = 1000 - 1200- 0(backlog from prev week) = -200 Week 3 Backlog = 1500 - 1600 - 200(backlog from prev week) = -300 I hope this helps — Neil S

sergiom sergiom · Accepted Answer · 2021-04-16T09:03:31

In DAX there are no loops and no recursion, this means that we must write some very ugly loop unrolling dax code. This is a minimal implementation of a working measure, but to make it working in the general case the BacklogWeekNN variables must be added until reaching the maximum possible week depth in the model.

Backlog = 
VAR MinWeek = CALCULATE(MIN( T[Week] ), REMOVEFILTERS( T ) )
VAR MaxWeek = MAX( T[Week] )
VAR TAggregated = 
ADDCOLUMNS(
    CALCULATETABLE( VALUES( T[Week] ), T[Week] <= MaxWeek, REMOVEFILTERS( T ) ),
    "@Capacity", CALCULATE( SUM( T[CAPACITY] ), ALLEXCEPT( T, T[Week], T[Factory ID] ) ),
    "@Request", CALCULATE( SUM( T[Request] ), ALLEXCEPT( T, T[Week], T[Factory ID] ) )
    )
VAR BacklogWeek00 = SUMX( FILTER( TAggregated, T[Week] = MinWeek ), [@Capacity] - [@Request] ) + 0
VAR BacklogWeek01 = SUMX( FILTER( TAggregated, T[Week] = MinWeek + 1), [@Capacity] - [@Request] ) + IF(BacklogWeek00 > 0, 0, BacklogWeek00) 
VAR BacklogWeek02 = SUMX( FILTER( TAggregated, T[Week] = MinWeek + 2), [@Capacity] - [@Request] ) + IF(BacklogWeek01 > 0, 0, BacklogWeek01)  
VAR BacklogWeek03 = SUMX( FILTER( TAggregated, T[Week] = MinWeek + 3), [@Capacity] - [@Request] ) + IF(BacklogWeek02 > 0, 0, BacklogWeek02)  
VAR Result = IF(BacklogWeek03 > 0, 0, BacklogWeek03)
RETURN Result

This way we can obtain the desired resulting matrix using a slicer to select the factories (I'm afraid there are a few errors in the expected result samples int the question)

Edit: I used 'FILTER( TAggregated, T[Week] = MinWeek )' instead of the equivalent CALCULATE/CALCULATTABLE DAX code to avoid context transition happening and because the weeks table has very few rows to be iterated (I can imagine a maximum of a few tens or at maximum hundreds of rows if keeping a few years history)

PowerBI calculate overflow backlog in measure

1 Answers