Forum Discussion

PrakashPalle's avatar
PrakashPalle
Helper II
2 years ago
Solved

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

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 TimeFrom Date TimeTo Date Time IDCondition
11/16/2020 0:0111/16/2020 0:0011/18/2020 0:006107With In Range
7/9/2020 14:457/10/2020 0:007/15/2020 0:003734Before Range
3/7/2020 22:003/7/2020 0:003/17/2020 0:001478With In Range
11/8/2020 1:5210/27/2020 0:0011/4/2020 0:005717After Range
4/16/2020 15:304/12/2020 0:004/22/2020 0:002039With In Range
5/31/2020 3:476/1/2020 12:006/5/2020 12:002834Before 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")))

 

Need Help
  • dulan_kavinda's avatar
    dulan_kavinda
    2 years ago

    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"
    )

     

1 Reply

  • dulan_kavinda's avatar
    dulan_kavinda
    Helper I
    2 years ago

    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"
    )