Date and Time

[Sujit_Thakur]

Dear DAX Experts ,

I have a doubt , i dont know if this is possible or not with DAX 
I have a table 

Primary Dateand time	Secondary Date and time
8/2/17 2:30 AM	8/2/17 4:30 AM
8/2/17 4:35 AM	8/4/17 5:30 AM
8/4/17 5:25 AM	8/4/17 6:30 AM

This table is recorded one by one i mean secondary date and time  in row one must be logically less than primary date and time from second row ,and similarly ahead .
But some time while recording data , i have found some overlapping ,
So how can i flag such events 

My desired output will look something like this 

Primary Dateand time	Secondary Date and time	Overlap Flag
8/2/17 2:30 AM	8/2/17 4:30 AM	0
8/2/17 4:35 AM	8/4/17 5:30 AM	0
8/4/17 5:25 AM	8/4/17 6:30 AM	1

Please help

Regards 
Sujit Thakur 

[Greg_Deckler]

@Sujit_Thakur - Shouldn't both the last two entries be flagged for overlap?

[Sujit_Thakur]

Dear @Greg_Deckler ,
Only one must be flagged on from which overlapping occured .

[Greg_Deckler]

@Sujit_Thakur - So that is based on Index then? So the rule is as long as a row does not overlap any previous rows (based on index) then it should not be flagged, correct? 

[Sujit_Thakur]

Dear @Greg_Deckler 
Yes , actually the example which i gave above also contains a index column for every model .

e.g
Pvin  primary date    Secondary date 

1
1
1

1
1
2
2
2

So here the catch is those objects with same Pvin must be checked for no overlapping , one more point i would like to add that there is no index other than pvin

[harshnathani]

Hi @Sujit_Thakur ,

USe DateDIFF function.

DATEDIFF ( <Date1>, <Date2>, <Interval> )

The result is positive if Date2 is larger than Date1.

Incase you get a negative value, flag it as 1 using IF statement.

https://dax.guide/datediff/

Regards,

Harsh Nathani

[harshnathani]

Hi @Sujit_Thakur

Create a Column

Previous SecondaryDate = 
CALCULATE(MAX('Table'[Secondary Date and time]), FILTER('Table','Table'[Secondary Date and time] < EARLIER('Table'[Secondary Date and time])))

Column = IF( DATEDIFF('Table'[Primary Dateand time  ],'Table'[Previous SecondaryDate],SECOND) < 0 , 0,1)

Regards,

Harsh Nathani

[amitchandak]

@Sujit_Thakur , I solved the overlapping problem in this file. Refer if this can help

https://www.dropbox.com/s/1mlq21o1xjuw8il/overlappingdates.pbix?dl=0