Forum Discussion

Maverikk's avatar
Maverikk
Frequent Visitor
1 year ago
Solved

Join DimDate to Fact where DateTime is inbetween StatusFrom and StatusTo

I have a Fact table which has many rows, with a StatusFrom (e.g. 2025-03-17 12:54:55) and StatusTo (e.g. 2154:01:01 00:00:00) and I am looking to select ONLY the rows where SELECTEDDATE (+23:59:59) i...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    Maverikk Try using

    DAX
    IsDateBetweenStatus =
    VAR SelectedDate = SELECTEDVALUE('Dim Date'[Date]) -- Get the selected date from the slicer or context
    VAR DateTimeAtMidnight = SelectedDate + TIME(23, 59, 59) -- Combine the date with '23:59:59' time
    RETURN
    IF (
    DateTimeAtMidnight >= 'Fact Device Status'[StatusFromDateTime] &&
    DateTimeAtMidnight <= 'Fact Device Status'[StatusToDateTime],
    1, -- Returns 1 if the selected date (with '23:59:59' time) is between StatusFrom and StatusTo
    0 -- Returns 0 otherwise
    )

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

Maverikk Try using

DAX
IsDateBetweenStatus =
VAR SelectedDate = SELECTEDVALUE('Dim Date'[Date]) -- Get the selected date from the slicer or context
VAR DateTimeAtMidnight = SelectedDate + TIME(23, 59, 59) -- Combine the date with '23:59:59' time
RETURN
IF (
DateTimeAtMidnight >= 'Fact Device Status'[StatusFromDateTime] &&
DateTimeAtMidnight <= 'Fact Device Status'[StatusToDateTime],
1, -- Returns 1 if the selected date (with '23:59:59' time) is between StatusFrom and StatusTo
0 -- Returns 0 otherwise
)