How to do date relationship with fact table

[ashmitp869]

Hi All,

I have a Pay Period table which contains data like below

How to do the relationship between fact MpSummaryHours fact table and Pay Period, so that

The date field [Pay Period End Date] can be use to check i,e if Year 2024 and Month 02 is selected.

The [Pay Period End Date] should do a checking from [Date From] to [Date To]

i.e date 31/01/2024 to 27/02/2024

Any suggestion ?

[Anonymous · Accepted solution]

Hi @ashmitp869 ,

Thanks @DataNinja777  for the quick reply. I have some other thoughts to add:

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.  

(2) We can create a measure. 

Employee Hrs Measure = 
var _min=CALCULATE(MIN('Pay Period'[Date From]),ALLSELECTED('Pay Period'))
var _max=CALCULATE(MAX('Pay Period'[Date To]),ALLSELECTED('Pay Period'))
RETURN CALCULATE(SUM('MpSummaryHours'[Employee Hrs]),FILTER(ALL('MpSummaryHours'),[Pay Period End Date]<=_max && [Pay Period End Date] >= _min))

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[DataNinja777]

Hi @ashmitp869 

When there are multiple date fields in your fact tables (such as above, where there are "Date From" and "Date To"), the correct data model is to have a Calendar table and your fact table with multiple date fields as disconnected tables.  By having them as disconnected tables, you are free to do any time dimension analysis of your fact tables.  This is akin to headcount analysis where employee number is dependent on both the start date and the end date in relation to any particualr point in time in the calendar table.  The article below discusses this technique in relation to multiple date fields in employee tables.  

https://p3adaptive.com/finding-the-magic-part-2-on-the-way-to-data-happiness/

Best regards,

[ashmitp869]

Hi ,

Sorry may be I explain it wrong.

My report needs to be filter from Year and Month from Pay Periods (marked in Yellow) and the Employees Hrs needs to be calculated depending on Year selections(2024) and Month (02) 

Report filter selection made by user

The it should do the SUM(Employee Hrs) for  [Pay Period End Date] >='31/01/2024' and [Pay Period End Date] <='27/02/2024'

which is defined in [Pay Period] table ( see screenshot 2)

see screenshot 2