Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
This is the most simple solution and it perfectly solves my problem!
Thank you for your help.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
19 | |
17 | |
16 | |
16 |
User | Count |
---|---|
50 | |
24 | |
21 | |
17 | |
16 |