topic Re: Checking if selected date slicer is between 2 date columns in DAX Commands and Tips

Checking if selected date slicer is between 2 date columns

wjlee — Fri, 20 Dec 2024 04:35:39 GMT

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.

Re: Checking if selected date slicer is between 2 date columns

dharmendars007 — Fri, 20 Dec 2024 07:13:17 GMT

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

Re: Checking if selected date slicer is between 2 date columns

Anonymous — Mon, 23 Dec 2024 06:44:11 GMT

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