Forum Discussion
Calculate expected customer lead time
Hello Team,
It has been a lot of time I am trying to create this measure but have not gotten an expected result. I came here with lot of hopes
Have a look at below table
| Plant | Material Id | Week No | Receipt Quantity | On Hand Quantity | Lead Time |
| 1010 | XYZ | 1 | 0 | 0 | 0 |
| 1010 | XYZ | 2 | 0 | -30 | 2 |
| 1010 | XYZ | 3 | 10 | -90 | 8 |
| 1010 | XYZ | 4 | 20 | -50 | 3 |
| 1010 | XYZ | 5 | 0 | 0 | 0 |
| 1010 | XYZ | 6 | 0 | -40 | 1 |
| 1010 | XYZ | 7 | 50 | -150 | 10 |
| 1010 | XYZ | 8 | 0 | -380 | 18 |
| 1010 | XYZ | 9 | 0 | 0 | 0 |
| 1010 | XYZ | 10 | 70 | 0 | 0 |
| 1010 | XYZ | 11 | 0 | 0 | 0 |
| 1010 | XYZ | 12 | 0 | 0 | 0 |
| 1010 | XYZ | 13 | 0 | 0 | 0 |
| 1010 | XYZ | 14 | 0 | 0 | 0 |
| 1010 | XYZ | 15 | 20 | 0 | 0 |
| 1010 | XYZ | 16 | 50 | 0 | 0 |
| 1010 | XYZ | 17 | 60 | 0 | 0 |
| 1010 | XYZ | 18 | 0 | 0 | 0 |
| 1010 | XYZ | 19 | 0 | 0 | 0 |
| 1010 | XYZ | 20 | 70 | 0 | 0 |
| 1010 | XYZ | 21 | 50 | 0 | 0 |
| 1010 | XYZ | 22 | 25 | 0 | 0 |
| 1010 | XYZ | 23 | 30 | 0 | 0 |
| 1010 | XYZ | 24 | 0 | 0 | 0 |
| 1010 | XYZ | 25 | 0 | 0 | 0 |
| 1010 | XYZ | 26 | 10 | 0 | 0 |
The above table is a replica of my power bi file, where I am trying to calculate the Lead Time column.
Below are conditions and calculations.
1. if On-hand Quantity is "0" or positive, Lead Time will be "0"
2 If On-hand Quantity is Negative then, it should show the count of weeks to wait to get On Hand Quantity equal or greater than the sum of Receipt Quantity starting from next week.
Example 1:- how 2 is there in Lead time column in week no 2?
On hand quantity is -30, so let's start suming up Receipt Quantity values from next week that is 3 , if you sum week 4 and 5 Receipt Quantity values then it gives 30 which is equal to 30(Ignore Negative sign) in On Hand Quantity at week 2, So Lead time column shows count of weeks to wait, here it is 2 (week 4 and 5)
Example 2 :- How 10 is calculated in week 7?
On hand quantity in week 7 is -150, so let's start suming up Receipt Quantity values from next week that is 8 , if you sum week 8,9,10,11,12,13,14,15,16 and 17 Receipt Quantity values then it gives 200 which is greater than -150(Ignore Negative sign) in On Hand Quantity at week 7.
- Anonymous4 years ago
Hi Anonymous ,
Dax is not available. And Dax can't do iterations. You can achieve it in Power Query.
I have found a similar post, please refer to if to see if it helps you.
Power Query - Cumulated row, if a certain value is matched
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- AnonymousNot applicable
Hi Anonymous ,
Dax is not available. And Dax can't do iterations. You can achieve it in Power Query.
I have found a similar post, please refer to if to see if it helps you.
Power Query - Cumulated row, if a certain value is matched
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- AnonymousNot applicable
Thank you