Forum Discussion

arvbot_LE's avatar
arvbot_LE
Frequent Visitor
1 year ago

DAX Carton Backlog Calculation with maximum Capacity per Day

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 restri...
arvbot_LE's avatar
arvbot_LE
Frequent Visitor
1 year ago

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's avatar
Dangar332
Resident Rockstar
1 year ago

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.