Day_of_Short and Recovery_Date calculationbased on Product, Location,Planned Date

[Anonymous]

Hi ,

I have been struggingto calculate the Day_of_short. I am so stuck to understand the logic that I should be using in order to get this.
I have a table that consists of Product, Location, Planned_Dates and I should be calculating the days_of_short based on the shortage amount and planned dates. 

Below is the table I mentioned about.

Product	Location	Date	End_inv	Short
400269	1110	08-10-2022	30	No
400269	1110	08-11-2022	-2	Yes
400269	1110	08/13/2022	10	No
400270	1110	08/14/2022	-10	Yes
400270	1110	08/15/2022	-5	Yes
400270	1110	08/16/2022	5	No

So the shortage is basically if End_inv <0 then Yes else No. 
Now what I am suppossed to calculate is the Day_of_short and Recovery_date. I need the day_of_short to be the first negative end_inv's  date and the recovery_date to be positive end_inv's date.
Here is the table for better understanding.

Product	Location	Day of short	Recovery date
400269	1110	08-11-2022	08/13/2022
400270	1110	08-14-2022	08/16/2022

Hoping that someone can help me on this.
Thanks in advance.
@tamerj1 @Henrykong_ @Rayshin @amitchandak @johnt75 

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Please use the measure.

Recovery Date = 
var short  = [Day of short]
RETURN
    CALCULATE(MIN('Storage'[Date]),'Storage'[End_inv]>0 , 'Storage'[Date] > short)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I am getting an error using the measure. Placeholder error

[tamerj1]

Hi @Anonymous 
Please see updated file https://www.dropbox.com/t/1iNZqE5ri7ebdW85

Recovery Date = 
VAR PositiveTable = FILTER ( 'Storage', 'Storage'[Date] > [Day of short] && [Ending_Inventory] > 0 )
VAR LaststPositiveDate = MAXX ( PositiveTable, 'Storage'[Date] )
RETURN
    LaststPositiveDate

[tamerj1 · Accepted solution]

Hi @Anonymous 
Haveyou checked the updated solution?

[tamerj1]

Hi @Anonymous 
Here is a sample file with the solution https://www.dropbox.com/t/EvvwbADpWVW2A0Gn

Day of short = 
VAR NegativeTable = FILTER ( 'Storage', 'Storage'[End_inv] < 0 )
VAR FirstNegativeDate = MINX ( NegativeTable, 'Storage'[Date] )
RETURN
    FirstNegativeDate

Recovery Date = 
VAR PositiveTable = FILTER ( 'Storage', 'Storage'[End_inv] > 0 )
VAR LaststPositiveDate = MAXX ( PositiveTable, 'Storage'[Date] )
RETURN
    LaststPositiveDate

[Anonymous]

Hey @tamerj1  this works but there is some logic that i think I need to change in order to get the right recovery date. 
When I use the formula for recovery date, it is taking the dates that have positive End_inv.  The problem here is there is no point in having the recovery date when there is no day of short. 
And I need the recovery date to be the next date that falls after the day of short. 
I have attached a sample screenshot of my data for your reference.  The top table is the one that I am working on. There are recovery dates for different plant which do not have the day of short. And at the bottom table there is a negative End_inv where the Day of short is 30-12-2021, I want the recovery date to be next date where the End_inv is positive i.e. 01-01-2022. How do I modify the formula based on the requirements i mentioned.

Attaching the modified version of the table that I have posted earlier.

Product	Location	Date	End_inv	Short
400269	1110	08-10-2022	30	No
400269	1110	08-11-2022	-2	Yes
400269	1110	08/13/2022	10	No
400269	1110	08/15/2022	3	No
400270	1110	08/14/2022	-10	Yes
400270	1110	08/15/2022	-5	YEs
400270	1110	08/16/2022	5	No
400270	1110	08/18/2022	6	No

Product	Location	Day of short	Recovery date	NO of days short
400269	1110	08-11-2022	08/13/2022	2
400270	1110	08-14-2022	08/16/2022	2

Hope you understood what I meant.

Thanks in advance

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Please use the measure.

Recovery Date = 
var short  = [Day of short]
RETURN
    CALCULATE(MIN('Storage'[Date]),'Storage'[End_inv]>0 , 'Storage'[Date] > short)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-kkf-msft This works!! Thank you so much.
But sorry for coming back with another question again.
Now I have an other requirement with the same usecase which I find is again complicated.
So I'll walk you through the measure we are looking for
The requirement here is that we want the Day of shorts if the Product and Location are same. 
i.e. as the product here has End_inv falling negative for 3 dates we would want to calculate the 2 dates where the End_inv is falling short.

Product	Location	Date	End_inv	Short	Short1
400269	1110	08-10-2022	30	No	0
400269	1110	08-11-2022	-2	Yes	1
400269	1110	08/13/2022	10	No	0
400269	1110	08/15/2022	3	No	0
400269	1110	08/14/2022	-10	Yes	1
400269	1110	08/15/2022	-5	YEs	0
400269	1110	08/16/2022	5	No	0
400269	1110	08/18/2022	6	No	0

Or we thought we can work on this logic in different way i.e. achieve the Short1 first. Short1 is nothing but if Short is No then 0, but if there is a Yes after No the Short1 should be 1 else 0. The row next to the 1 should definetly be 0. 
To explainn in better way .

 In the table there are 3 dates where end_inv is negative but we would want to calculate only 2 dates leaving the date after the second short.
Here is the table I am looking for :

Product	Location	Day of short	Recovery date	NO of days short
400269	1110	08-11-2022	08/13/2022	2
400269	1110	08-14-2022	08/16/2022	2

I hope you understood the way I explained the query.
I have no idea if I'm looking at this in a tough way or this is tough but yeah I couldn't solve this.
Thanks in advance.