Forum Discussion
Solved
DATEDIFF
Hi Team, I have the following data I just wanted to count those records with status = "IN" and the time difference between the dates > 30 minutes for the same mobile number. In this example, ...
I do thing in little steps, so this is just my style, and maybe someone can add to this to fix the potential In/In/out data bug?
We need to define an 'OutTime' then you can DateDifff and go from there...
OutTime = IF('Table'[Status] = "IN",
CALCULATE(MIN('Table'[DateTime]), FILTER('Table', 'Table'[Mobile#] = EARLIER('Table'[Mobile#]) && 'Table'[Status] = "OUT" && 'Table'[DateTime] > EARLIER('Table'[DateTime]))))
Once you have the OutTimes pulled into a new column, you can DateDiff the two columns (blanks in the OutTime will Blank the DateDiff)
DateDiff = DATEDIFF('Table'[DateTime], 'Table'[OutTime], MINUTE)
Then finally, just IF statement the count (which could easily be combined with the last step...
Over30? = IF('Table'[DateDiff] > 30, 1)