The Date Where There Is Enough Supply To Satisfy Dem-CodePudding

CodePudding user response：

Given my understanding of what you want for results, the following Power Query M code should return that.

Read the code comments, statement names and explore the Applied Steps to understand the algorithm.

let

//Read in the data tables
//could have them in separate querries
    Source = Excel.CurrentWorkbook(){[Name="Demand"]}[Content],
    Demand = Table.TransformColumnTypes(Source,{{"item", type text}, {"Qty", Int64.Type}, {"Date", type date}}),

//make demand values negative
    #"Transform Demand" = Table.TransformColumns(Demand,{"Qty", each _ * -1}),

    Source2 = Excel.CurrentWorkbook(){[Name="Supply"]}[Content],
    Supply = Table.TransformColumnTypes(Source2,{{"item", type text},{"Qty", Int64.Type},{"Supply date", type date}}),
    #"Rename Supply Date Column" = Table.RenameColumns(Supply,{"Supply date","Date"}),

//Merge the tables and sort by Item and Date
    Merge = Table.Combine({#"Rename Supply Date Column", #"Transform Demand"}),
    #"Sorted Rows" = Table.Sort(Merge,{{"item", Order.Ascending}, {"Date", Order.Ascending}}),

//Group by Item
//Grouped running total to find first positive value
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"item"}, {
        {"First Date", (t)=> let 
            #"Running Total" = List.RemoveFirstN(List.Generate(
                ()=>[rt=t[Qty]{0}, idx=0],
                each [idx]<Table.RowCount(t),
                each [rt=[rt] t[Qty]{[idx] 1}, idx=[idx] 1],
                each [rt]),1),
            #"First non-negative" = List.PositionOfAny(#"Running Total", List.Select(#"Running Total", each _ >=0), Occurrence.First)
        in t[Date]{#"First non-negative" 1}, type date}})
in
    #"Grouped Rows"

Supply

Demand

Results

CodePudding user response：

I did this in Excel formula rather than using powerquery - there will be a powerquery equivalent but I'm not very fluent in DAX yet.

You need a helper column - could do without it but everything's much more readable if you have it. In sheet Supply (2), cell E2, enter the formula:

=SUMIFS(Supply!B:B; Supply!C:C;"<=" & C2;Supply!A:A;A2)-SUMIFS(Dem!B:B;Dem!C:C;"<=" & C2;Dem!A:A;A2)

and copy downwards. This can be described as Total supply up to that date minus total demand up to that date. In some cases this will be negative (where there's more demand than supply).

Now you need to find the date of the first non-negative value for that. First create a unique list of the items - I put it on the same sheet in the range G2:G6. Then in H2, the formula:

=MINIFS(C:C;A:A;G2;E:E;">=" & 0)

and copy downwards.