Dax statement to identify instances where comparing two different dates in different columns

[Anonymous]

Hi

Im trying to write a code that can identify where the End Date is within 2 days of the next No's Start Date and to flag that for example

No	Start Date	End Date
100	23/03/2021	24/03/2021
200	25/03/2021	30/03/2021
100	26/03/2021	28/03/2021
300	1/04/2021	3/04/2021
300	10/04/2021	11/04/2021
No	Start Date	End Date	Within 2 days for same No.
100	23/03/2021	24/03/2021	Yes
200	25/03/2021	30/03/2021	No
100	26/03/2021	28/03/2021	Yes
300	1/04/2021	3/04/2021	No
300	10/04/2021	11/04/2021	No

[VahidDM]

@Anonymous 

BTW, you can find the days between End and next Start date with this code:

Within 2 days for same No. = 
Var _END = 'Table'[End Date]
Var _NextStart = CALCULATE(min('Table'[Start Date]),filter(all('Table'),'Table'[Start Date]>EARLIER('Table'[Start Date])&&'Table'[No]=EARLIER('Table'[No])))
Var _Diff = DATEDIFF(_END,_NextStart,DAY)
return
_Diff

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[VahidDM]

HI @Anonymous 

How did you calculate YES for second line of 100 with 28-Mar-21 end and no other line in the table?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

So the yes is because of line 1 and line 3 is taking leave within two days, by comparing the end date of each number (ID 100) and comparing it to the next occurance for that number (ID100) if the start date is within 2 days. Hope that makes sense.

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Please try the following formula:

Measure =
VAR lastenddate =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[No] = MAX ( 'Table'[No] )
                && 'Table'[End Date] < MAX ( 'Table'[End Date] )
        ),
        'Table'[End Date]
    )
VAR nextstartdate =
    MINX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[No] = MAX ( 'Table'[No] )
                && 'Table'[Start Date] > MAX ( 'Table'[Start Date] )
        ),
        'Table'[Start Date]
    )
VAR E_N_S = DATEDIFF ( MAX ( 'Table'[End Date] ), nextstartdate, DAY )
VAR S_L_E = DATEDIFF ( lastenddate, MAX ( 'Table'[Start Date] ), DAY )
RETURN
    IF ( E_N_S <= 2 && S_L_E <= 2 && E_N_S + S_L_E <> BLANK (), "Yes", "No" )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.