Join DimDate to Fact where DateTime is inbetween StatusFrom and StatusTo

[Maverikk]

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) is between StatusFrom and StatusTo.  This FACT is a fact showing the status changes for a case over a period of time, and we only want to see the status's as as 23:59:59 of whichever selected date we use, so we can look at this retrospectively too.  

I have followed all of the tutorials and cannot get anything to work as planned.

I have got this far:

Is Date Between Status 2 =

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 >= SELECTEDVALUE('Fact Device Status'[StatusFromDateTime]) &&

    DateTimeAtMidnight <= SELECTEDVALUE('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

)

but it returns this data when selected today: .

DimDate is joined to Fact on Date = StatusFromDate.  I guess I am doing something wrong but need some direction please.

[v-vpabbu]

Hi @Maverikk,

Thanks @bhanu_gautam for Addressing the issue.

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @Maverikk,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @Maverikk,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[bhanu_gautam · Accepted solution]

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