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 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.

 

DateCartons IncomingCartons ProcessedCartons Backlog
190900
211010010
313010040
410510045
59510040
650900

 

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.

5 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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'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
        Icon for Resident Rockstar rankResident 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.



  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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

      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 ...