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
    )

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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