Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 IdWeek NoReceipt QuantityOn Hand QuantityLead Time
1010XYZ1000
1010XYZ20-302
1010XYZ310-908
1010XYZ420-503
1010XYZ5000
1010XYZ60-401
1010XYZ750-15010
1010XYZ80-38018
1010XYZ9000
1010XYZ107000
1010XYZ11000
1010XYZ12000
1010XYZ13000
1010XYZ14000
1010XYZ152000
1010XYZ165000
1010XYZ176000
1010XYZ18000
1010XYZ19000
1010XYZ207000
1010XYZ215000
1010XYZ222500
1010XYZ233000
1010XYZ24000
1010XYZ25000
1010XYZ26100

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.

 

 

 

  • Anonymous's avatar
    Anonymous
    4 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

  • Anonymous's avatar
    Anonymous
    Not 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.