How to connect table with Month and year data with calendar table in Power Bi

2
votes

I'm trying to connect a table (Payroll table) who has No. of labour hours by Months of a year. For example:

  1. 2017 Jan 10000
  2. 2017 Feb 12000

I want to connect this table to my calendar table to perform some calculation on fact table data which i have already connected to my calendar table.

Can anyone please help me creating a relation between Calendar table and Payroll table.

Thanks in Advance!!

1
powerbidaxssas-tabular

1 Answers

2
votes

Assuming that you have a Date column in the Calendar table, you can use the following DAX expression to create a new column:

Year Month = 
YEAR('Calendar'[Date]) & " " & 
FORMAT(DATE(1, MONTH('Calendar'[Date]), 1), "MMM")

year month

Then you can create a one-to-many relationship between the Year Month column and the Month column from your Payroll table:

relationship