cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ericlday Frequent Visitor
Frequent Visitor

Compare dates in different columns of same table

I am cleaning up some data. I am trying to find start/end dates that overlap. Here is a table showing customer ID, start date, and end date.

 

Screen Shot 2019-03-05 at 2.53.03 PM.png

 

We should only have one job open per customer at a time. As you can see, the first end date is after the second start date. One of these was inputted wrong or we had two jobs open at the same time (which is not best practice). I'm trying to produce a list of all these overlaps to investigate them further.

 

I've ventured down the path of ranking the start dates and end dates per customer and then seeing if rank 2 start is before rank 1 end, etc., but I get stuck. I'm not convinced that's the best way either.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Compare dates in different columns of same table

Hi @ericlday ,

 

If I understand your requirement correctly that you want to find the date which is inputted wrong.

 

If it is, you could create an index column in Query Editor and then create the calcualted columns below.

 

Column =
CALCULATE (
    FIRSTNONBLANK ( 'Table1'[Start Date], 1 ),
    FILTER ( 'Table1', 'Table1'[Index] = EARLIER ( 'Table1'[Index] ) + 1 )
)

Column 2 =
IF ( 'Table1'[Column] < 'Table1'[End Date], FALSE (), TRUE () )

Here is the output.

 

compare.PNG

 

If you still need help, please share your desired output so that we could help further on it.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Community Support Team
Community Support Team

Re: Compare dates in different columns of same table

Hi @ericlday ,

 

If I understand your requirement correctly that you want to find the date which is inputted wrong.

 

If it is, you could create an index column in Query Editor and then create the calcualted columns below.

 

Column =
CALCULATE (
    FIRSTNONBLANK ( 'Table1'[Start Date], 1 ),
    FILTER ( 'Table1', 'Table1'[Index] = EARLIER ( 'Table1'[Index] ) + 1 )
)

Column 2 =
IF ( 'Table1'[Column] < 'Table1'[End Date], FALSE (), TRUE () )

Here is the output.

 

compare.PNG

 

If you still need help, please share your desired output so that we could help further on it.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ericlday Frequent Visitor
Frequent Visitor

Re: Compare dates in different columns of same table

@v-piga-msft 

 

This definitely gets me closer. My issue comes when it applies to my database of 100,000 customers.

 

Screen Shot 2019-03-06 at 9.23.24 AM.png

 

If we use a straight index for all customers, I start getting a lot of falses because the first start date of a customer gets compared to the last end date of the previous customer.

 

I keep trying to figure out how to have an index per customer id. So customer ID 100046 would have index 1-4 then customer ID would have index 1-4. Then we would just have to work in a condition to return an error or something else when index + 1 does not exist.

 

Does this make sense?

Community Support Team
Community Support Team

Re: Compare dates in different columns of same table

Hi  @ericlday ,

 

You could try to create a group index with Dax formula like below.

 

index =
RANKX (
    FILTER ( 'Table1', 'Table1'[ID] = EARLIER ( Table1[ID] ) ),
    'Table1'[Start Date],
    ,
    ASC
)

Then create the calcualted column which mentioned in my first reply.

 

Here is the output.

 

Capture.PNG

 

Hope this can help you!

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ericlday Frequent Visitor
Frequent Visitor

Re: Compare dates in different columns of same table

@v-piga-msft ,

 

I really appreciate you working through this with me. We are so close.

 

Below is a screenshot of what I'm getting when I apply to my whole dataset.

 

For some reaseon, it is populating Column with dates from the 90s. Does this issue come from the data not being sorted by ID then by Start Date? I have done this sort inside of edit queries, but when I close and apply it does not apply that sort. The sort you see here is done by ID after the query is applied.

 

Screen Shot 2019-03-07 at 9.12.36 AM.png

ericlday Frequent Visitor
Frequent Visitor

Re: Compare dates in different columns of same table

@v-piga-msft ,

 

As a reference, here's 2 screenshots. 

 

First is after I close and apply. It defaults to be sorted by end date.

 

Second is inside of edit queries. I sort it using SQL before I even see it by ID and start date.

 

Screen Shot 2019-03-07 at 9.45.35 AM.pngScreen Shot 2019-03-07 at 9.39.28 AM.png

ericlday Frequent Visitor
Frequent Visitor

Re: Compare dates in different columns of same table

@v-piga-msft 

 

I got a solution for what I needed. It's a little messy, but got what I wanted.

 

I used this other solution to get the index based on idea column created...

 

https://community.powerbi.com/t5/Desktop/Custom-column-Index-or-Ranking-by-other-column/td-p/33864

 

From here, I created a new custom column that was Index + 1.

 

I then duplicated the query. In one query, I made a merged column that was "ID,Index." In the other query, I made a merged column column that was "ID, Index+1." I then merged the queries with a left outer join where "ID,Index" = "ID, Index+1" and expanded to include end date. This then gave me the start date with the previous end date in the same row. Conditional column to test if it came before.

ericlday Frequent Visitor
Frequent Visitor

Re: Compare dates in different columns of same table

@v-piga-msft 

 

Sorry. Hit post by accident when I tried to post picture.

 

Hope my explanation makes sense of my process. Again, it's not ideal with duplicated queries, but gets the job done for now.Screen Shot 2019-03-07 at 3.27.50 PM.png

Community Support Team
Community Support Team

Re: Compare dates in different columns of same table

Hi @ericlday ,

 

Sorry for the delay.

 

It seems that you have solved your problem.

 

Please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.