Pick a day when item will be out-of-stock

[Anonymous]

I have stock/shipping-plan/receiving-plan QTY by item id and date in psi table.

item | date             | type        | qty

a123 | 2021-06-22 | stock      | 15

a123 | 2021-06-23 | receiving | 20

a123 | 2021-06-24 | shipping | -50

a123 | 2021-06-25 | receiving | 20

a123 | 2021-06-26 | shipping | -10

a456 | 2021-06-22 | stock      | 20

a456 | 2021-06-23 | shipping | -10

a456 | 2021-06-24 | shipping | -15

* now = 2021-06-22

To predict stock qty, I added quick measure : 

stock qty =
CALCULATE(
SUM('psi'[qty]),
FILTER(
ALLSELECTED('psi'[date]),
ISONORAFTER('psi'[date], MAX('psi'[date]), DESC)
)
)
item | date             | type        | qty | stock qty

a123 | 2021-06-22 | stock      | 15   | 15

a123 | 2021-06-23 | receiving | 20  | 35

a123 | 2021-06-24 | shipping | -50 | -15

a123 | 2021-06-25 | receiving | 20  | 5

a123 | 2021-06-26 | shipping | -10 | -5

a456 | 2021-06-22 | stock      | 20   | 20

a456 | 2021-06-23 | shipping | -10 | 10

a456 | 2021-06-24 | shipping | -15 | -5

To alert a day when each item will out of stock, I'd like to filter rows by next conditions:
1. stock qty is negative
2. date is the most earilest in each item
Like this:
item | date             | type        | qty | stock qty

a123 | 2021-06-24 | shipping | -50 | -15

a456 | 2021-06-24 | shipping | -15 | -5

I tyied adding another DAX calculation and using top-N filter but they didn't work applopriately.

[CNENFRNL · Accepted solution]

PQ solution is way much easier,

let
    Source = PLAN,
    #"Grouped Rows" = Table.Group(Source, {"item"}, {{"ar", each Table.RemoveColumns(_, "item")}}),
    Stock = Table.TransformColumns(
        #"Grouped Rows",
        {"ar", each
                let rs = Table.ToRecords(_)
                in List.Select(
                    List.Accumulate(rs, {}, (s,c) => s & {c & [stk = (List.Last(s)??[stk=0])[stk] + c[qty]]}),
                    each [stk]<=0
                ){0}
        }
    ),
    #"Expanded ar" = Table.ExpandRecordColumn(Stock, "ar", {"date", "type", "qty", "stk"}, {"date", "type", "qty", "stk"})
in
    #"Expanded ar"

[Anonymous]

This is the most simple solution and it perfectly solves my problem!

Thank you for your help.

[Jihwan_Kim]

Qty alert measure : =

VAR currentitem =

MAX ( Items[item] )

VAR firstdateminusstock =

MINX (

GROUPBY (

FILTER (

ADDCOLUMNS (

SUMMARIZE (

CALCULATETABLE ( ALL ( data ), Items[item] = currentitem ),

Items[item],

Dates[Date]

),

"@stockqty", [Stock qty measure :]

),

[@stockqty] < 0

&& Dates[Date] >= TODAY ()

),

Items[item],

"@mindate", MINX ( CURRENTGROUP (), Dates[Date] )

),

[@mindate]

)

VAR qtyalert =

CALCULATE (

[Qty measure :],

KEEPFILTERS ( FILTER ( ALL ( Dates ), Dates[Date] = firstdateminusstock ) )

)

RETURN

IF( ISFILTERED(Dates[Date]),

qtyalert)

Stock qty alert measure : =

VAR currentitem =

MAX ( Items[item] )

VAR firstdateminusstock =

MINX (

GROUPBY (

FILTER (

ADDCOLUMNS (

SUMMARIZE (

CALCULATETABLE ( ALL ( data ), Items[item] = currentitem ),

Items[item],

Dates[Date]

),

"@stockqty", [Stock qty measure :]

),

[@stockqty] < 0

&& Dates[Date] >= TODAY ()

),

Items[item],

"@mindate", MINX ( CURRENTGROUP (), Dates[Date] )

),

[@mindate]

)

VAR stockqtyalert =

CALCULATE (

[Stock qty measure :],

KEEPFILTERS ( FILTER ( ALL ( Dates ), Dates[Date] = firstdateminusstock ) )

)

RETURN

IF( ISFILTERED(Dates[Date]),

stockqtyalert

)

https://www.dropbox.com/s/wxr5szbnbg8e193/okunishi.pbix?dl=0 

[Anonymous]

Jihwan_Kim,

Thank you for your reply and sample pbix file!

This is fit to my issue but too complex for me.

I will try to understand what do measures calculate.

[Tanushree_Kapse]

Hi @Anonymous ,
You can try creating a measure:
Alert= IF(AND(SELECTEDVALUE(stock qty)<0,EARLIEST(SELECTEDVALUE(Date))),"Out of Stock")

I guess this is what you need.

[Anonymous]

Thank you for quick reply!

When I added your measure, app told me that EARLIER/EARLIEST function refer to non-existing row-context. (This is not acculate error message because I'm using it in ja-JP)

I will take this opportunity to learn about EARIEST function.