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.
Hello,
I need help, I'm trying to implement a stock projection calculation in place.
What I'm looking to do is to say if I have enough stock to despatch a product. For that i'm looking at previous orders for the same product. If the due date of the despatch is the same the idea is to look at the earliest activationdate to determine an order.
I have a column showing the available stock for the particular product. This value should be decreasing in regards of the previous orders and not stay the same like it is right now.
I've attached a sample file as well as an excel spreadsheet with the calculation I'd like to achieve
status | duedate | Activationdate | traderid | sorder | itemnumber | partid | remaingquantity | availableforassembly | Calculation | Result | Despatch? |
ACTIVE | 01/10/2021 | 2021-09-30 08:11:00.000 | CUST1 | S-0001 | 7 | SRP124 | 100 | 1000 | / | 1000 | Ok |
ACTIVE | 02/10/2021 | 2021-09-30 08:12:00.000 | CUST3 | S-0002 | 15 | SRP124 | 200 | 1000 | 1000-100-300 | 600 | Ok |
ACTIVE | 03/10/2021 | 2021-09-30 08:23:00.001 | CUST2 | S-0003 | 1 | SRP124 | 1050 | 1000 | 1000-100-200-300 | 400 | Nok |
ACTIVE | 04/10/2021 | 2021-09-30 10:12:00.001 | CUST1 | S-0004 | 1 | SRP124 | 150 | 1000 | 1000-100-200-1050-300 | -650 | Nok |
ACTIVE | 01/10/2021 | 2021-09-30 10:23:00.002 | CUST1 | S-0005 | 3 | SRP124 | 300 | 1000 | 1000-100 | 900 | Ok |
Solved! Go to Solution.
Hi, @ifenek
Try to create a measure like this:
_Result =
var _max=MAX ( 'Table'[availableforassembly] )
var _count=
CALCULATE (
DISTINCTCOUNT ( 'Table'[Activationdate] ),
ALLEXCEPT ( 'Table', 'Table'[duedate] )
)
var duedate=
CALCULATE (
SUM ( 'Table'[remaingquantity] ),
FILTER ( ALL ( 'Table' ), 'Table'[duedate] < MAX ( 'Table'[duedate] ) )
)
var duedate_Act=
CALCULATE (
SUM ( 'Table'[remaingquantity] ),
FILTER (
ALL ( 'Table' ),
'Table'[duedate] = MAX ( 'Table'[duedate] )
&& 'Table'[Activationdate] < MAX ( 'Table'[Activationdate] )
)
)
return
IF(_count>1,_max-duedate-duedate_Act,_max-duedate)
resutl:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @ifenek
Try to create a measure like this:
_Result =
var _max=MAX ( 'Table'[availableforassembly] )
var _count=
CALCULATE (
DISTINCTCOUNT ( 'Table'[Activationdate] ),
ALLEXCEPT ( 'Table', 'Table'[duedate] )
)
var duedate=
CALCULATE (
SUM ( 'Table'[remaingquantity] ),
FILTER ( ALL ( 'Table' ), 'Table'[duedate] < MAX ( 'Table'[duedate] ) )
)
var duedate_Act=
CALCULATE (
SUM ( 'Table'[remaingquantity] ),
FILTER (
ALL ( 'Table' ),
'Table'[duedate] = MAX ( 'Table'[duedate] )
&& 'Table'[Activationdate] < MAX ( 'Table'[Activationdate] )
)
)
return
IF(_count>1,_max-duedate-duedate_Act,_max-duedate)
resutl:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |