Condition based on date time field fall between two date time columns

PrakashPalle — Mon, 24 Jun 2024 16:26:55 GMT

Hi Power BI Experts,

I want to calculate condition column based on the date time field will fall in between, before & after conditions. Sample data below. Expected output in "condition" column. Please provide the dax expression. I tried the below dax code but it doesn't given correct output what i am looking. Thanks in advance.

Date Time	From Date Time	To Date Time	ID	Condition
11/16/2020 0:01	11/16/2020 0:00	11/18/2020 0:00	6107	With In Range
7/9/2020 14:45	7/10/2020 0:00	7/15/2020 0:00	3734	Before Range
3/7/2020 22:00	3/7/2020 0:00	3/17/2020 0:00	1478	With In Range
11/8/2020 1:52	10/27/2020 0:00	11/4/2020 0:00	5717	After Range
4/16/2020 15:30	4/12/2020 0:00	4/22/2020 0:00	2039	With In Range
5/31/2020 3:47	6/1/2020 12:00	6/5/2020 12:00	2834	Before Range

Dax Expression:

Condition =
vAR _DATE='Date Time'[Date Time]
var _With_In=COUNTROWS(FILTER('Date Time','Date Time'[From Date Time]<=_DATE && 'Date Time'[To Date Time]>=_DATE))
var _Before=COUNTROWS(FILTER('Date Time','Date Time'[From Date Time]<='Date Time'[Date Time]))
var _After=COUNTROWS(FILTER('Date Time','Date Time'[To Date Time]>=_DATE))
RETURN
IF(_With_In=1,"With in",IF(_After=1,"After",IF(_Before=1,"Before")))

Re: Condition based on date time field fall between two date time columns

dulan_kavinda — Mon, 24 Jun 2024 17:06:45 GMT

Right click on the table and add "New Column". Use below DAX (Modify the table name).

Condition =
VAR DateTimeValue = 'Table_name'[Date Time]
VAR FromDateTimeValue = 'Table_name'[From Date Time]
VAR ToDateTimeValue = 'Table_name'[To Date Time]
RETURN
SWITCH(
TRUE(),
DateTimeValue >= FromDateTimeValue && DateTimeValue <= ToDateTimeValue, "With In Range",
DateTimeValue < FromDateTimeValue, "Before Range",
DateTimeValue > ToDateTimeValue, "After Range"
)

topic Re: Condition based on date time field fall between two date time columns in DAX Commands and Tips

Condition based on date time field fall between two date time columns

Re: Condition based on date time field fall between two date time columns