DAX to find last date where sum went negative?

[bi_quest18]

Hi All,

New to DAX and struggling to find a calculation to perform the following:

Data is like this:

FiscalDate CurrentInventoryAmt SalesAmt RemainderAmt 

20180714    1000                        100              900
20180707     1000                        200              700
20180701    1000                         1000           -300
 20180624   1000                          0                -300  

Couple of notes:

1. The Fiscal date needs to look backwards (Descending order), fiscal dates are the week start dates.

2. RemainderAmt is a running total calculated as taking the remainderAmt of the last week and subtracting SalesAmt.

I need to find:

For each fiscal date, i need to find the number of days where the RemainderAmt went negative

For e.g.

For 20180714, RemainderAmt went negative in 20180701, so the measure needs to return 14 (days between 20180714 and 20180701)

For 20180707, the measure will return 7 days 

For 20180701, the measure will return 0 days since the remainder is already negative.

Any guidance will be appreciated!

Thanks

[PattemManohar]

@bi_quest18 Please try add to add below three fields as "New Column"

MaxDate (Negative amount)

MaxDate = 
VAR _MaxDate = CALCULATE(MAX(Test37[FiscalDate]),FILTER(Test37,SIGN(Test37[RemainingAmt])=-1))
VAR _MaxDateFinal = DATE(LEFT(_MaxDate,4),MID(_MaxDate,5,2),RIGHT(_MaxDate,2))
RETURN _MaxDateFinal

FiscalDate conversion to proper Date datatype

FiscalDateNew = DATE(LEFT(Test37[FiscalDate],4),MID(Test37[FiscalDate],5,2),RIGHT(Test37[FiscalDate],2))

Then, here is the Days logic

Days = IF(Test37[MaxDate]>Test37[FiscalDateNew],0,DATEDIFF(Test37[MaxDate],Test37[FiscalDateNew],DAY))

[bi_quest18]

Hi Pattermmanohar, This is great. thanks!

Since the data set is huge = 2 billion+ rows, the current cube size is 127GB.

Is it possible to do this in measures? I am trying to minimize calculated columns to conserve the footprint of the deployed cube. 

Thanks again