Date difference in same column for different stage

[Anonymous]

Hi,

  I need a Calculated column for Date difference of same date based on different stages and ID.The stage will start at any level, for initial stage date difference should be 0 for each ID. The Final column value should as in Date Difference

[PattemManohar · Accepted solution]

@Anonymous Please try this as a "New Column"

DateDiff = 
VAR _PrevDate = LOOKUPVALUE(Test48CummDateDiff[Date],Test48CummDateDiff[ID],Test48CummDateDiff[ID],Test48CummDateDiff[Stage],Test48CummDateDiff[Stage]-1) 
VAR _PrevDateClean = IF(_PrevDate=BLANK(),Test48CummDateDiff[Date],_PrevDate)
RETURN DATEDIFF(_PrevDateClean,Test48CummDateDiff[Date],DAY)

[PattemManohar · Accepted solution]

@Anonymous Please try this as a "New Column"

DateDiff = 
VAR _PrevDate = LOOKUPVALUE(Test48CummDateDiff[Date],Test48CummDateDiff[ID],Test48CummDateDiff[ID],Test48CummDateDiff[Stage],Test48CummDateDiff[Stage]-1) 
VAR _PrevDateClean = IF(_PrevDate=BLANK(),Test48CummDateDiff[Date],_PrevDate)
RETURN DATEDIFF(_PrevDateClean,Test48CummDateDiff[Date],DAY)

[Anonymous]

@PattemManohar Thanks for the effort. But as output am getting different values as below. I need a date difference of previous stages for each ID

[PattemManohar · Accepted solution]

@Anonymous 🙂 It's working fine, As per the screenshot - Your data contains 23-Aug-2017 for Stage 3, ID 421 (Row 3) that's why you are seeing huge date difference. But where as your initial sample data contains 23-Aug-2018.

[Anonymous]

@PattemManohar Thanks:-)