topic Re: A date column adjacent to a date column in DAX Commands and Tips

A date column adjacent to a date column

Sofiya — Wed, 13 Mar 2024 04:07:22 GMT

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.

Re: A date column adjacent to a date column

talespin — Wed, 13 Mar 2024 06:04:46 GMT

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)

Re: A date column adjacent to a date column

SamInogic — Wed, 13 Mar 2024 06:28:18 GMT

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,

)

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/