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
dieterdn
Frequent Visitor

Relationship between date table and table with start and end date

Hi

I'm trying to create a relationship between 2 tables:

- the date table

- the table with the core data of the report

Every record in the second table has a start and end date.

In case of a change in the data, a new record will be created that will have a start date on the date of the change, and an end date 31/12/9999. The previous record will be "closed" by updating the end date to the day before the date of the change.

i.e.. a table is storing data of a company's employees, including every employees direct manager:

 

start date   end date       employee name   direct manager

1/1/2020   31/12/9999          X                             Y

 

when the direct manager changes to Z on 25/05, the table looks like this:

start date    end date       employee name   direct manager

1/1/2020    24/5/2020          X                              Y

25/5/2020  31/12/9999        X                              Z

 

when i then filter on a date, the goal is to only show the data from the employee table where my chosen date falls between the start and end period

is there a way to do this?

I already have a measure with the headcount where today falls between these dates, but that means I can only show data that is valid today, and I lose the historic data.

 

I didn't find anything regarding this in the forum.

 

Thanks a lot for any input!

1 ACCEPTED SOLUTION
4 REPLIES 4
Anonymous
Not applicable

Hi, @amitchandak 

I've tried this solution when having csv of excel files as a dataset which seems to work fine. However when I'm working with an online database using direct query it doesn't. I'm trying to count the number of id's between the start date and end date, but with direct query it's only counting the id on the start date or not whatsoever. 

Any ideas why this method doesn't work while working with online datasets?

I appreciate any input!

Hi @amitchandak 

 

I will definitely use these insights!

however it doesn't seem there can be multiple records for the same employuee in this example?

@dieterdn , as the formula do not use the employee id to join, and there are no overlapping dates, the similar formula should work

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.