Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
wjlee
Frequent Visitor

Checking if selected date slicer is between 2 date columns

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 -

wjlee_0-1734668410915.png

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

wjlee_3-1734668783940.png


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.

wjlee_4-1734668912088.png

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.

wjlee_5-1734669007896.png

 

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

wjlee_2-1734668664252.png


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.



 

2 REPLIES 2
Anonymous
Not applicable

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.

vyaningymsft_0-1734936140117.png

Best Regards,
Yang

Community Support Team

dharmendars007
Super User
Super User

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 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.