Need help adding a calendar table to my data model

Question

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 · Answer

Hello&nbsp;Anonymous,&nbsp;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 ?&nbsp;https://radacad.com/all-in-one-script-to-create-calendar-table-or-date-dimension-using-dax-in-power-bihttps://radacad.com/power-bi-date-or-calendar-table-best-method-dax-or-power-queryyou have the M query code and the DAX query code,&nbsp;&nbsp;&nbsp;

anonymous · Answer

I have already created the calendar table.&nbsp; 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 · Answer

mmm ok sorry,i checked the image just now,&nbsp;your model is somehow complexthere 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.&nbsp;&nbsp;&nbsp;i think you can create inactive relationship between the calendar and the 2 other tables .&nbsp;and in the dax measure,&nbsp;whenver you need to use that relationship,&nbsp;&nbsp;write&nbsp; :&nbsp;calculate (&nbsp; aggregation&nbsp; ,&nbsp; userelationship (&nbsp; id 1 , id2 )&nbsp;)&nbsp;https://learn.microsoft.com/en-us/dax/userelationship-function-daxhttps://dax.guide/userelationship/&nbsp;or you can ignore creating inactive relationships and just propagate the filters using treatas function in DAX .&nbsp;https://learn.microsoft.com/en-us/dax/treatas-functionhttps://dax.guide/treatas/hope this helps .&nbsp;best regards&nbsp;&nbsp;

Forum Discussion

Need help adding a calendar table to my data model

3 Replies

Related content

Calendar Slicer with Different Business Calendars

calendar table

Custom calendar

Calendar Table

Adding columns