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.
Hi,
I would like to seek for help in creating recursive inventory stock logic.
Currently this is the example table we have.
Product | Country | Year/Month | Initial Stock | Stock on hand | intransit | demand | Balance |
A | ABC | 2022/10 | 0 | 100 | 50 | 30 | 120 |
A | ABC | 2022/11 | 120 | 30 | 50 | 30 | 170 |
A | ABC | 2022/12 | 170 | 30 | 50 | 30 | 220 |
We would like to create 2 recursive dax
1. Balance = initial stock + stock on hand + intransit - demand
2. Initial stock = (last month's) Balance + stock on hand + intransit - demand
The known data in the table are
1. Initial stock of 2022/10 : 0 (as the starting point)
2. Stock on hand, intransit, demand, product, country in all periods
Thanks for your help.
Solved! Go to Solution.
Hi,
please check the below picture and the attached pbix file.
Balance measure: =
CALCULATE (
SUMX ( Data, Data[Stock on hand] + Data[Intransit] - Data[Demand] ),
FILTER ( ALL ( Data ), Data[Date] <= MAX ( Data[Date] ) ),
VALUES ( Data[Country] ),
VALUES ( Data[Product] )
)
Initial stock measure: =
VAR _previousdate =
MAXX (
FILTER (
ALL ( Data ),
Data[Product] = MAX ( Data[Product] )
&& Data[Country] = MAX ( Data[Country] )
&& Data[Date] < MAX ( Data[Date] )
),
Data[Date]
)
RETURN
IF (
SELECTEDVALUE ( Data[Date] ) = DATE ( 2022, 10, 1 ),
0,
CALCULATE (
[Balance measure:],
FILTER ( ALL ( Data ), Data[Date] = _previousdate ),
VALUES ( Data[Country] ),
VALUES ( Data[Product] )
)
)
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.
Thanks. It is very helpful.
Hi,
please check the below picture and the attached pbix file.
Balance measure: =
CALCULATE (
SUMX ( Data, Data[Stock on hand] + Data[Intransit] - Data[Demand] ),
FILTER ( ALL ( Data ), Data[Date] <= MAX ( Data[Date] ) ),
VALUES ( Data[Country] ),
VALUES ( Data[Product] )
)
Initial stock measure: =
VAR _previousdate =
MAXX (
FILTER (
ALL ( Data ),
Data[Product] = MAX ( Data[Product] )
&& Data[Country] = MAX ( Data[Country] )
&& Data[Date] < MAX ( Data[Date] )
),
Data[Date]
)
RETURN
IF (
SELECTEDVALUE ( Data[Date] ) = DATE ( 2022, 10, 1 ),
0,
CALCULATE (
[Balance measure:],
FILTER ( ALL ( Data ), Data[Date] = _previousdate ),
VALUES ( Data[Country] ),
VALUES ( Data[Product] )
)
)
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.
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |