A date column adjacent to a date column

[Sofiya]

ORD_ID	MLSTN_CD	RECEIVED DATE	ACTUAL DATE
100HOB786	140	13-Mar-2023	21-Mar-2023
100HOB786	142	13-Mar-2023	7-Apr-2023
100HOB786	142	17-Mar-2023	8-Apr-2023
100HOB786	142	22-Mar-2023	11-Apr-2023
100HOB786	3	22-Mar-2023	22-Mar-2023
100HOB786	142	23-Mar-2023	10-Apr-2023
100HOB786	3	24-Mar-2023	22-Mar-2023
100HOB786	142	27-Mar-2023	10-Apr-2023
100HOB786	142	28-Mar-2023	9-Apr-2023
100HOB786	142	30-Mar-2023	9-Apr-2023
100HOB786	142	31-Mar-2023	11-Apr-2023
100HOB786	142	3-Apr-2023	12-Apr-2023
100HOB786	142	4-Apr-2023	12-Apr-2023
100HOB786	142	5-Apr-2023	11-Apr-2023
100HOB786	142	6-Apr-2023	11-Apr-2023
100HOB786	142	7-Apr-2023	10-Apr-2023
100HOB786	142	10-Apr-2023	9-Apr-2023
100HOB786	2	10-Apr-2023	10-Apr-2023

Query is in the image from the same table attched and pasted here.

[SamInogic]

Hi @Sofiya ,

You can also try below DAX expression for the requirement,

AlertStatus =

VAR TargetDate = 'Table'[ACTUAL DATE] - 5

VAR ReceivedAlert =

    CALCULATE(

        COUNTROWS('Table'),

        'Table'[RECEIVED DATE] = TargetDate

    )

VAR AdjacentDate =

    IF(

        ReceivedAlert > 0,

        CALCULATE(

            VALUES('Table'[ACTUAL DATE]),

            'Table'[RECEIVED DATE] = TargetDate

        )

    )

VAR WithinRange =

    IF(

        NOT(ISBLANK(AdjacentDate)),

        IF(

            'Table'[ACTUAL DATE] - AdjacentDate <= 1 && 'Table'[ACTUAL DATE] - AdjacentDate >= -1,

            1,

            0

        ),

        0

    )

RETURN

    WithinRange

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[talespin]

hi @Sofiya 

There were lot of questions, so I made some assumptions.

1. Arrival date of Parcel is where MLSTN_CD value is minimum.

2. What if there are multiple records for 5th April(Received Date)?

Create this as Measure.

Flag =

VAR _SelOrder = SELECTEDVALUE(Orders[ORD_ID])

VAR _MinMLSTN = CALCULATE( MIN(Orders[MLSTN_CD]), REMOVEFILTERS(), Orders[ORD_ID] = _SelOrder)

VAR _ActualDate = CALCULATE( MIN(Orders[ACTUAL DATE]), REMOVEFILTERS(), Orders[ORD_ID] = _SelOrder && Orders[MLSTN_CD] = _MinMLSTN)

VAR _AlertDate = (_ActualDate - 5)

VAR _ActualDate2 = CALCULATE( MIN(Orders[ACTUAL DATE]), REMOVEFILTERS(), Orders[ORD_ID] = _SelOrder && Orders[RECEIVED DATE] = _AlertDate)

RETURN IF( _ActualDate2 = (_ActualDate + 1) || _ActualDate2 = (_ActualDate - 1), 1, 0)