Checking if selected date slicer is between 2 date columns

[wjlee]

Hi all, I am facing difficulty figuring out how to check if a selected value of a date slicer is in between 2 date columns.

Use Case -
When the user selects a date in the date slicer, there should be a measure to check that the selected date is between the StartDate column and EndDate column of the latest row created (indicated by RankByProjectDate = 1). It doesn't need to check if the RankByProjectDate =/= 1.
If it is between the StartDate and EndDate, return DistinctCount of the ProjectId (or just return 1).
If it is not between the StartDate and EndDate, return 0.

The Problem -
I have a fact table called "ValuesGen" that contains the following data -

There is a single select Date slicer based on my Date table -

Expected Results -
1) If the user selects the date as "20 December 2024", the DateCheck measure should return 1 (as indicated in the image below), as RankByProjectDate = 1, and the selected date is between the StartDate and EndDate.

2) If the user selects the date as "5 September 2024", the DateCheck measure should return 1 (as indicated in the image below), as RankByProjectDate = 1, and the selected date is between the StartDate and EndDate.

The Model -
My Date table is connected to the "ValuesGen" table via a one-to-many relationship as illustrated below -

What I've Tried -
I've tried implementing the following measures but clearly DateCheck isn't working, and I'm not sure how to proceed. Could I get some advice on fixing this or is there an easier/better way to do it?

RankByProjectDate = 
RANKX(
    FILTER(
        ValuesGen, // Ignores filters from the slicer on this table
        ValuesGen[ProjectID] = EARLIER(ValuesGen[ProjectID])
    ),
    ValuesGen[CreatedDatetime],
    ,
    DESC
)

_DateCheck = 
VAR SelectedDate = SELECTEDVALUE('_DateTable'[Date])

VAR _StartDate = CALCULATE(MAX(ValuesGen[StartDate]), FILTER (ValuesGen, ValuesGen[RankByProjectDate] = "1"))

VAR _EndDate = CALCULATE(MAX(ValuesGen[EndDate]), FILTER (ValuesGen, ValuesGen[RankByProjectDate] = "1"))

VAR _Value = CALCULATE(DISTINCTCOUNT(ValuesGen[ProjectId]), FILTER (ValuesGen, ValuesGen[RankByProjectDate] = "1"))

RETURN
IF(SelectedDate >= _StartDate && SelectedDate <= _EndDate, 1 , 0)

Any help is very much appreciated.

Thank you.

[Anonymous]

Hi, @wjlee 

Thanks for @dharmendars007's reply. He provides a way for you to try to see if it works, and if it doesn't fit your needs, can you explain why after selecting 2024/12/20 it has to be the yellow two line DateCheck output 1, which requires a logic.

Best Regards,
Yang
Community Support Team

[dharmendars007]

Hello @wjlee , 

You can try the below corrected bersion of DAX..

DateCheck =
VAR SelectedDate = SELECTEDVALUE('Date'[Date]) -- Get the selected date from the slicer
RETURN
IF (ISBLANK(SelectedDate),
0, -- No date is selected, return 0
IF (SUMX(
FILTER(MainCalendar,
SelectedDate >= MainCalendar[StartDate] &&
SelectedDate <= MainCalendar[EndDate]),1) > 0,
1, -- Date falls within the range
0 -- Date does not fall within the range))

Outer IF Conditions>> Returns 1 if at least one match is found, Returns 0 otherwise.

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN