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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Shelley
Continued Contributor
Continued Contributor

How to Check a Date is within the Date Range Slicer Selection?

There's probably a nice trick here, but I'm struggling with it. We are using the standard Date Range Slicer. The calendar date is connected to the create date on a Ticket Table. I cannot make an active connection between another date in a Document table and the calendar because it would create a circular relationship. I want to check a Start Date and an End Date in the Document table to see if these dates fall within the range of those selected with the Date Range Slicer to determine if a particular document should be included in a calculation. Does anyone know how to do this - to check these dates against those selected? Thanks in advance!

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @Shelley,

 

Check the answer I gave on this post in this case I'm calculating the number of days but the formula can be readjusted to give a YES / NO text instead. the adjusted formula is below:

 

YES_NO = 
VAR START_DATE =
    MIN ( DimDate[Date] )
VAR END_DATE =
    MAX ( DimDate[Date] )
RETURN
  IF(  SUMX (
        SUMMARIZE (
            FactTable;
            FactTable[StartDate];
            FactTable[EndDate];
            "DAYS"; IF (
                FactTable[StartDate] < START_DATE
                    && FactTable[EndDate] < START_DATE
                    || FactTable[StartDate] > END_DATE
                    && FactTable[EndDate] > END_DATE;
                0;
                DATEDIFF (
                    IF ( FactTable[StartDate] < START_DATE; START_DATE; FactTable[StartDate] );
                    IF ( FactTable[EndDate] > END_DATE; END_DATE; FactTable[EndDate] );
                    DAY
                )
            )
        );
        [DAYS]
    ) > 0 ; "YES"; "NO")

In this case you will get the following result.

 

dates_start.png

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

Hi @Shelley,

 

Check the answer I gave on this post in this case I'm calculating the number of days but the formula can be readjusted to give a YES / NO text instead. the adjusted formula is below:

 

YES_NO = 
VAR START_DATE =
    MIN ( DimDate[Date] )
VAR END_DATE =
    MAX ( DimDate[Date] )
RETURN
  IF(  SUMX (
        SUMMARIZE (
            FactTable;
            FactTable[StartDate];
            FactTable[EndDate];
            "DAYS"; IF (
                FactTable[StartDate] < START_DATE
                    && FactTable[EndDate] < START_DATE
                    || FactTable[StartDate] > END_DATE
                    && FactTable[EndDate] > END_DATE;
                0;
                DATEDIFF (
                    IF ( FactTable[StartDate] < START_DATE; START_DATE; FactTable[StartDate] );
                    IF ( FactTable[EndDate] > END_DATE; END_DATE; FactTable[EndDate] );
                    DAY
                )
            )
        );
        [DAYS]
    ) > 0 ; "YES"; "NO")

In this case you will get the following result.

 

dates_start.png

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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