Forum Discussion

ashmitp869's avatar
ashmitp869
Responsive Resident
2 years ago
Solved

How to do date relationship with fact table

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 ?

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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. 

3 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    2 years ago

    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's avatar
      ashmitp869
      Responsive Resident
      2 years ago

      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

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.