Need help adding a calendar table to my data model

[Anonymous]

I currently have 3 tables that I need to use to create what I call Employee Productivity.  Those tables are FMS Kronos, PM Productivity, and WO Productivity.  In each of those tables, there are dates associated with when the person worked, when the person did the PM, and when the person did the WO.  I am trying to attach a calendar table so that when I want to get the Employee Productivity for a certain period I can do so.

Here is the 3 measure that i have created to make up the Employee Productivity%

Total WO  Prod Labors hours YTD =

CALCULATE(

    SUM('WO Productivity'[Labor_Hours]),

        'WO Productivity'[User Type when WOs was open] = "Shared Services"&&

     YEAR('WO Productivity'[date_opened]) IN {2024}

    )

YTD PM Prod Labor Hours =

CALCULATE(

    SUM('PM Productivity'[Labor_hours]),

        'PM Productivity'[User Type when PMs was open] = "Shared Services"&&

        YEAR('PM Productivity'[open_date]) IN {2024}

    )

FMS Tech PROD Hours =

CALCULATE(

    SUM('FMS Kronos'[Rounded_Clocked_Hour_Num]),

    'FMS Kronos'[Kronos_Pay_Code] ="PROD"&&

    'FMS Kronos'[Dept_Num]<>900&&

    'FMS Kronos'[User Type FMS]="Shared Services",

    YEAR('FMS Kronos'[Valid_From_Date]) IN {2024}

)

Here is my data model as it is today:

[Daniel29195]

Hello @Anonymous, 

can you please refer to this article and tell me if it works for you , or do you need a specific kind od calendar table ? 

https://radacad.com/all-in-one-script-to-create-calendar-table-or-date-dimension-using-dax-in-power-bi

https://radacad.com/power-bi-date-or-calendar-table-best-method-dax-or-power-query

you have the M query code and the DAX query code, 

[Anonymous]

I have already created the calendar table.  The issue i am running into is being able to connect it to the 3 tables that i am using for the 3 measures that make up the Employee Productivity %

[Daniel29195]

mmm ok sorry,

i checked the image just now, 

your model is somehow complex

there is a lot of many to many with bidirectional filters which i assume is the reaon you cant connect the calendar table to all other tables. 

i think you can create inactive relationship between the calendar and the 2 other tables . 

and in the dax measure, 

whenver you need to use that relationship, 

write  : 

calculate (  aggregation  ,  userelationship (  id 1 , id2 ) 
) 
https://learn.microsoft.com/en-us/dax/userelationship-function-dax
https://dax.guide/userelationship/

or you can ignore creating inactive relationships and just propagate the filters using treatas function in DAX . 

https://learn.microsoft.com/en-us/dax/treatas-function
https://dax.guide/treatas/

hope this helps .

best regards