Unable to establish any relationship with fact table with date table ?

[ashmitp869]

Hi

I do have different requirement ,where I have a fact table MpSummaryHours where I need to calculate Employee Hours with filter from date field [Pay Period End Date] from the same fact table.

The [Pay Period] gives the date range i,e [Date From] to [Date To] depending on user selection 

So , if a user select Year [2024] and Month [02] coming from Pay Period like below

So - in this case the measures should calculate [Employee Hours] where  [Pay Period End Date] >='31/01/2024' and [Pay Period End Date] <='27/02/2024'.

How to create a relationship between them.

Please find the sample [Pay Period] attached. https://github.com/suvechha/samplepbi 

Period Month Year	Date From	Date To	Year Month	Month	Year
2024-01	3/01/2024	16/01/2024	2024-01	01	2024
2024-02	17/01/2024	30/01/2024	2024-01	01	2024
2024-03	31/01/2024	13/02/2024	2024-02	02	2024
2024-04	14/02/2024	27/02/2024	2024-02	02	2024
2024-05	28/02/2024	12/03/2024	2024-03	03	2024
2024-06	13/03/2024	26/03/2024	2024-03	03	2024
2024-07	27/03/2024	9/04/2024	2024-04	04	2024
2024-08	10/04/2024	23/04/2024	2024-04	04	2024
2024-09	24/04/2024	7/05/2024	2024-04	04	2024
2024-10	8/05/2024	21/05/2024	2024-05	05	2024
2024-11	22/05/2024	4/06/2024	2024-05	05	2024
2024-12	5/06/2024	18/06/2024	2024-06	06	2024
2024-13	19/06/2024	2/07/2024	2024-06	06	2024
2024-14	3/07/2024	16/07/2024	2024-07	07	2024
2024-15	17/07/2024	30/07/2024	2024-07	07	2024
2024-16	31/07/2024	13/08/2024	2024-08	08	2024
2024-17	14/08/2024	27/08/2024	2024-08	08	2024
2024-18	28/08/2024	10/09/2024	2024-09	09	2024
2024-19	11/09/2024	24/09/2024	2024-09	09	2024
2024-20	25/09/2024	8/10/2024	2024-10	10	2024
2024-21	9/10/2024	22/10/2024	2024-10	10	2024
2024-22	23/10/2024	5/11/2024	2024-10	10	2024
2024-23	6/11/2024	19/11/2024	2024-11	11	2024
2024-24	20/11/2024	3/12/2024	2024-11	11	2024
2024-25	4/12/2024	17/12/2024	2024-12	12	2024
2024-26	18/12/2024	31/12/2024	2024-12	12	2024

@amitchandak will you please help me out in this situation ? I did see your abstract thesis video but unable to resolve this scenario

[h21]

Hi @ashmitp869, 

I think you have not created Date Table.

Try creating Date Table and make relationship of both the table with Date Table.

Maybe that will be able to provide you solution.

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Best Regards.

[v-shex-msft]

Hi @ashmitp869,

It sounds like a common multiple date filed analysis requirement, I’d like to suggest you can take a look at the Greg’s blog start/end date part if they suitable for your scenario:

Before You Post, Read This: start, end date 

Regards,

Xiaoxin Sheng