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
sosukeinu
Regular Visitor

Show records where date falls between two other dates

Hello,
I'm new to PowerBI and DAX, but I have experience using Python and R to work with data. I'm trying to find the best way to deal with this particular problem.

What I'd Like to do:
I have data that contains a number of education records like below:
ID       schyr       sem     start               end

-----------------------------------------------------
12       200203   1        01/01/2002   06/30/2002

12       200203   2        11/01/2002   06/30/2003

13       200203   1        01/01/2002   06/30/2002

13       200203   2        11/01/2002   06/30/2003

.....

I would like to be able to select an arbitrary date like 03/10/2002 and return all records where that date falls between start and end, and the ID is distinct. In the above example, these rows would be returned:
12       200203   1        01/01/2002   06/30/2002
13       200203   1        01/01/2002   06/30/2002

The Way I'm Currently Accomplishing This:

Currently I'm getting my records to a form where they are unique for ID, schyr, and sem. Then, I'm creating a vector of every date between start and end, and duplicating each row x times for the number of days, and appending these dates to the records. The issue with this approach is obvious. It must be done in R before bringing it into PowerBI,...and it takes a records set that has around 64,000 records (where each record means something obvious) and making it a record set with almost 1 million rows.

Any help or thoughts about better ways to do this would be greatly appreciated.
Thank you.

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@sosukeinu

 

In this scenario, you can generate a calendar table for populating slicer.

 

Calendar = CALENDAR("2002/1/1","2003/12/31")

Create a measure to get the selected date.

 

 

Selected Date = MAX('Calendar'[Date])

Then create a measure to compare the start date and end date of term with selected date.

 

 

Within Term = IF([Selected Date]>MAX(education[start]) && [Selected Date]<MAX(education[end]),1,0)

4.PNG

 

 

Now you can add [Within Term] measure into visual level filter.

 

5.PNG

 

6.PNG

 

Regards,

View solution in original post

1 REPLY 1
v-sihou-msft
Employee
Employee

@sosukeinu

 

In this scenario, you can generate a calendar table for populating slicer.

 

Calendar = CALENDAR("2002/1/1","2003/12/31")

Create a measure to get the selected date.

 

 

Selected Date = MAX('Calendar'[Date])

Then create a measure to compare the start date and end date of term with selected date.

 

 

Within Term = IF([Selected Date]>MAX(education[start]) && [Selected Date]<MAX(education[end]),1,0)

4.PNG

 

 

Now you can add [Within Term] measure into visual level filter.

 

5.PNG

 

6.PNG

 

Regards,

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.