DAX Carton Backlog Calculation with maximum Capacity per Day

[arvbot_LE]

Hi Guys,

I struggle for a while with this topic now, if it is even possible to calculate in Power BI. 

The situation: 

I have a table with a carton quantity per Date. Each day there is a restriction on how many cartons can be processed (e.g. 100 cartons). If there are more cartons than can be processed, the quantity is taken to the next day. Having this backlog for one day, is no problem, but this scenario can happen multiple days in a row. Adding new backlog each day, that will be processed once the "new cartons" are below our maximum capacity.

Date	Cartons Incoming	Cartons Processed	Cartons Backlog
1	90	90	0
2	110	100	10
3	130	100	40
4	105	100	45
5	95	100	40
6	50	90	0

In Excel it is easy, as I can simply sum for the processed Cartons: MIN( 100, "Today Cartons Incoming" +  "Yesterday Cartons Backlog") and Backlog : "Today Cartons Incoming" +  "Yesterday Cartons Backlog" - Today Processed

but in DAX I am currently not able to exit my current mode of thinking. 

Let me know, if you need more details and thanks for your help.

[Anonymous]

Hi, @arvbot_LE 

Based on your description, I created the following dataset:

Create the following two measures:

Measure = 
VAR _a = SUM('Table'[Cartons Incoming])
VAR _b = SUM('Table'[Cartons Processed])
VAR _c = _a -_b
RETURN _c

Measure 2 = CALCULATE([Measure],ALLSELECTED('Table'),'Table'[Date]<=SELECTEDVALUE('Table'[Date]))

Here are the results:

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[arvbot_LE]

Hi @Anonymous ,

this is the "easy" approach, which I have already in place, but does not solve my issue.

My issue is, that my cartons processed is not a given number. It is dependent on my incoming cartons of each day and the backlog of cartons from all days before. 

It is a recursion, which I assume is not possible in DAX, but maybe ...

[bhanu_gautam]

@arvbot_LE Create a calculated column for the daily processed cartons:

Cartons Processed =
VAR MaxCapacity = 100
VAR IncomingCartons = 'Table'[Cartons Incoming]
VAR PreviousBacklog =
CALCULATE(
SUM('Table'[Cartons Backlog]),
FILTER(
'Table',
'Table'[Date] = EARLIER('Table'[Date]) - 1
)
)
RETURN
MIN(MaxCapacity, IncomingCartons + COALESCE(PreviousBacklog, 0))

Create a calculated column for the daily backlog:

Cartons Backlog =
VAR MaxCapacity = 100
VAR IncomingCartons = 'Table'[Cartons Incoming]
VAR PreviousBacklog =
CALCULATE(
SUM('Table'[Cartons Backlog]),
FILTER(
'Table',
'Table'[Date] = EARLIER('Table'[Date]) - 1
)
)
VAR ProcessedCartons =
MIN(MaxCapacity, IncomingCartons + COALESCE(PreviousBacklog, 0))
RETURN
IncomingCartons + COALESCE(PreviousBacklog, 0) - ProcessedCartons

[arvbot_LE]

@bhanu_gautam , 

Thanks for the quick reply. I tried to implement these formulas. I understand mostly what you have written (never used COALESCE before) and are similar to what I tried so far.

Unfortunatly I do have a problem with the Carton backlog. In the variable for previous_backlog: I can't reference on the formula I am currently writing, so I exchanged it with the daily backlog, as I hadn't a better idea. For periods with 1 day backlog it works,

but the sample dataset below; I did the calculation by hand for comparison, this does not work properly now

sorry for the messy order of columns 🙂

I really apprechiate your experience. 

And it is possible that my approach is too complicated...

In the end I am looking for a stacked column chart, X axis as Date and as values once the processed cartons (today incoming + everything that could not be processed till this day) and second the total backlog I have left after what I processed today.

[Dangar332]

Hi, @arvbot_LE 

Please try below mention measures

day backlog = 
 var left_cartons = sum('Table'[Cartons incoming])-sum('Table'[Cartons Processed])
 RETURN
 IF(left_cartons<0,0,left_cartons)

Cartons backlog = 
SUMX(
    FILTER(ALL('Table'[Date],'Table'[Cartons incoming],'Table'[Cartons Processed]),'Table'[Date]<=MIN('Table'[Date])),
    var incoming = 'Table'[Cartons incoming]
    var processed =  'Table'[Cartons Processed]
    var result = incoming-processed
    RETURN result)

Best Regards,
Dangar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.