Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Sujit_Thakur
Solution Sage
Solution Sage

Date and Time

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 AM8/2/17 4:30 AM
8/2/17 4:35 AM8/4/17 5:30 AM
8/4/17 5:25 AM8/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 timeOverlap Flag
8/2/17 2:30 AM8/2/17 4:30 AM0
8/2/17 4:35 AM8/4/17 5:30 AM0
8/4/17 5:25 AM8/4/17 6:30 AM1

 




Please help

Regards 
Sujit Thakur 

7 REPLIES 7
Greg_Deckler
Community Champion
Community Champion

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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

@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? 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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
Community Champion
Community Champion

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

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)

 

 

1.jpg

 

 

Regards,

Harsh Nathani

 

 

amitchandak
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.