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
MagusTheWiser
New Member

Need to find if a selected date range is between two dates

Hello,

 

I searched but could not find a situation like mine.

 

I have two columns with dates, one is the date of the registration on a course, and the other is the expiration date of that license.

 

So i need to find if a selected date or interval is between the two dates, so i can find all those who are/were with a active registration on that period.

 

MagusTheWiser_1-1680114270667.png

 

 

For example:

 

In the slicer, If i select 25/03/2023 and 28/03/2023 it should show the seven entries with udemy but not any alura, because the interval is between the registration and expiration date of the udemy, but not enough for the alura to get active.

 

if i select 23/03/2023 and 30/03/2023 it is going to show all entries, because the period is between the dates in both columns.

 

if i select 05/05/2023 and 30/06/2023 it is going to show the seven entries again, because its after the expiration of the alura license, but the udemy period is active between the dates. 

 

Thanks in advance!

 

1 ACCEPTED SOLUTION
MohammadLoran25
Super User
Super User

Hi @MagusTheWiser ,

Assume your table name is "RegisExpireTable" and your date table name is "FullDDate" and also there is no relationship betwee these 2 tables. Then Follow Steps Below:

 

1-Creating a measure as :

 

IsActiveMeasure =
SUMX (
    RegisExpireTable,
    SWITCH (
        TRUE (),
        MIN ( FullDDate[Date] ) >= MIN ( RegisExpireTable[RegisterDate] )
            && MIN ( FullDDate[Date] ) <= MAX ( RegisExpireTable[ExpireDate] )1,
        MAX ( FullDDate[Date] ) >= MIN ( RegisExpireTable[RegisterDate] )
            && MAX ( FullDDate[Date] ) <= MAX ( RegisExpireTable[ExpireDate] )1
    )
)

 

2- Then Put this measure on a filter pane on your visual and set the advanced condition of "IS Greater than 0" on it and apply the filter. Then Based on Your date slicer Selection (from FullDDate Table), this measure would filter your data as you need.

 

If this answer solves your problem, please give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.

Regards,
Loran

 

 

View solution in original post

3 REPLIES 3
MohammadLoran25
Super User
Super User

Hi @MagusTheWiser ,

Assume your table name is "RegisExpireTable" and your date table name is "FullDDate" and also there is no relationship betwee these 2 tables. Then Follow Steps Below:

 

1-Creating a measure as :

 

IsActiveMeasure =
SUMX (
    RegisExpireTable,
    SWITCH (
        TRUE (),
        MIN ( FullDDate[Date] ) >= MIN ( RegisExpireTable[RegisterDate] )
            && MIN ( FullDDate[Date] ) <= MAX ( RegisExpireTable[ExpireDate] )1,
        MAX ( FullDDate[Date] ) >= MIN ( RegisExpireTable[RegisterDate] )
            && MAX ( FullDDate[Date] ) <= MAX ( RegisExpireTable[ExpireDate] )1
    )
)

 

2- Then Put this measure on a filter pane on your visual and set the advanced condition of "IS Greater than 0" on it and apply the filter. Then Based on Your date slicer Selection (from FullDDate Table), this measure would filter your data as you need.

 

If this answer solves your problem, please give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.

Regards,
Loran

 

 

Hi Mohammad, 

 

Are you able to provide a method for achieving this when there is duplicates in the ID column in the start/end date table?

 

In my scernario I need to be able to filter by a date range, and get a number of inspections that can be conducted for various assets within that period. eg see below if I filter by march 24, I want it to return that 1 inspection can be conducted in Melbourne and Brisbane Respectively. If I filter by June 24 all 3 assets could be inspected, so a count of 2 shows for Melbourne and 1 for brisbane. Using your method it counts from min to max for location so if I filter anywhere between 1 jan 24 and 1 jan 25 it will return 2 possible inspections for Melbourne even when the filter is within only one of the inspection windows (eg Feb 24). 

 

Earlierst Possible inspection dateLatest possible inspection dateLocationASSET Number
1/Jan/241/Jul/24Melbourne1
1/Apr/20241/Jan/25Melbourne2
1/Mar/241/Jul/24Brisbane3

Thank you Mohammad, 

 

It worked great!

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.