Issue with something similar to Running Total

[gjohnston2]

I have a unique request.  Here is the base setup - I have several sales order lines, each with a weight ordered amount for a particular product.  I also have a limited amount of available inventory to fill these orders.  Using predefined allocation rules I need to determine how many of these lines can be filled and apply conditional formatting to the lines in my table.  Green lines are those lines which can be filled and yellow for thise that can't be filled.  I have considered using a running total like scenario but can't build that logic.  Any thoughts and/or suggestions would be GREATLY appreciated!!

[gjohnston2]

[v-zhangti]

Hi, @gjohnston2 

You can try the following methods.

Measure = 
IF (
    CALCULATE (
        SUM ( Sales[Qty] ),
        FILTER ( ALL ( Sales ), [Qty] >= MAX ( Sales[Qty] ) )
    )
        < SELECTEDVALUE ( 'Available Inventory'[Weight] ),
    "Green",
    "Yellow"
)

Then apply Measure to the background color of each column in the conditional format.

The results are shown in the figure.

Is this the result you were expecting?

Best Regards,

Community Support Team _Charlotte

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

[gjohnston2]

Thanks - unfortunately this isn't going to work.  The Qty is a 2 part calculation.  The available amount is a static number based on what is currently in inventory and the Needed Weight by Max Date is based on a selected date.  We have to use the Weight Needed by Max Date compared to the available inventory.
Part 1
Total Weight Needed:=Calculate(SUM('Sales Order Lines'[Lbs Ordered]),'Sales Order Lines'[Open to Fill] = 1)

Part 2
Weight Needed by Max Date:=
Calculate(Total Weight Needed],
       Filter(
       All('Date')
       'Date'[Date] <= MAX('Date'[Date])
       && 'Date'[Date]
              >=MAX('Date'[Date])-90
))

I tried to reproduce your solution and got an error - here is what I tried and the error I got

Can Be Filled =

IF(

Calculate(Weight Needed by Max Date],

      FILTER( ALL ('Sales Order Lines',[Weight Needed by Max Date] >= MAX ('Sales Order Lines'[Weight
      Needed by Max Date]))

)

      < SELECTEDVALUE(Available Inventory'[Weight],

      "Green",

      "Yellow"

)

I get this error

[gjohnston2]

Sorry - no colors came through - Happy Customer line should be green and the other 2 should be yellow.

[gjohnston2]

Max Sales Order Date :	3/15/2022		We are looking at any orders with line items that need to be filled before on an this date
Available Inventory			This is a list of available invnetory
Item	Weight
ABCD	5,500.00
Current Demand			This is a list of Sales Orders with demand for the above item.  Green indicates it can be filled, Yellow indicates it can't be filled
Sales Order Number	Customer	Item	Requested Ship Date	Qty
SO12345	Happy Customer	ABCD	3/14/2022	5000
SO12346	Not Happy	ABCD	3/15/2022	1000
SO12347	Not Happy	ABCD	3/15/2022	750

[v-zhangti]

Hi, @gjohnston2 

Is it possible to provide some of the example data? And what do you expect the output to look like?

Best Regards