Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to build data model - fact tables with StartDate and EndDate - need to run report As of Date

I am trying to build a data model in SSAS Tabular where the central dimension table (Employee) is related to several fact tables which are historical audit tables with StartDate & EndDate.  Employee table is unique by EmployeeId.  All fact tables are related to Employee table in a many:1 relationship with PK/FK as EmployeeId.




I need to be able to create reports both as a snapshot in time (as of specific date) so the final result would look like this (only one result would be returned, just showing two examples here).



and also need to be able to return values between a given date range like this:



This is a very simplified version of the full data model (there are over 100 tables total, about 30% of them have the start/end date logic and others are simple dimension tables.  


The only possible solution I've come across is creating calculated tables for each of the fact tables and expanding out the dates to include a record for every day within the given date range like THIS but given almost 20 years worth of data I'm worried this will make the model so large it'll be slow and difficult to work with (in Direct Query mode with encrypted connection).  


Any suggestions here on a simpler approach?  Thanks all and Happy Holidays!





Super User
Super User

Hey @Anonymous ,


from reading your initial question my assumption is that you are facing the events-in-progress problem.

This post Events-In-Progress | Gerhard Brueckl on BI & Data ( is one of the most sophisticated post regarding this topic.

Nevertheless, it contains links to every essential post.

I recommend starting with the article by Jason Thomas (SQLJason).


If you need more help, consider to create a pbix that contains sample data, but still reflects your data model. Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.




Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Super User
Super User

Not applicable

Hi Amit,

Great post, but the problem I'm trying to figure out is much more complex.  I am not doing aggregations (count, sum, etc).  I am comfortable with that side of things.  What I need to do here is look up at any given point in time what the "current" value is for each table for any given employee so more of a "lookup" than a calculation.  

Helpful resources

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors