cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
MagnaNoahMcBrid
Frequent Visitor

Create Table for each Day in a Year

 

I have three Tables: 

 

Employee

Home Department

Rate

 

Home Department and Rate are child tables to Emplyoee and each have a value called "Effective Date", which determines when the Department or Rate of an Employee changed.

 

I would like to create a table for every employee with a record for each day of the year. Then I would look-up the Rate and Department that the Employee was working in at the given date based on the effective date. I would then have accurate cost allotment and rate calculations as Departments and Rates change during the year. Example

 

Employee Table       Department                                Rate

Emp 1                      Emp 1 - Dep1 - 1.1.2018            Emp 1 - Rate 1 - 1.1.2018

Emp 2                      Emp 2 - Dep1 - 1.1.2018            Emp 2 - Rate 1 - 1.1.2018

Emp 3                      Emp 1 - Dep2 - 1.2.2018            Emp 3 - Rate 2 - 1.1.2018

                                Emp 3 - Dep3 - 1.1.2018            Emp 1 - Rate 2 - 1.2.2018

 

Emp / Dept / Rate / Date Table

1.1.2018         Emp 1          Dep1         Rate 1

1.1.2018         Emp 2          Dep1         Rate 1       

1.1.2018         Emp 3          Dep1         Rate 2

1.2.2018         Emp 1          Dep2         Rate 2

1.2.2018         Emp 2          Dep1         Rate 1

1.2.2018         Emp 3          Dep1         Rate 2

1.3.2018         Emp 1          Dep2         Rate 2

1.3.2018         Emp 2          Dep1         Rate 1

1.3.2018         Emp 3          Dep1         Rate 2

 

Any advice for creating this with a query so that as Employees or Rates or Depts change, the new table changes as well.

 

1 REPLY 1
MagnaNoahMcBrid
Frequent Visitor

I have three Tables: 

 

Employee

Home Department

Rate

 

Home Department and Rate are child tables to Emplyoee and each have a value called "Effective Date", which determines when the Department or Rate of an Employee changed.

 

I would like to create a table for every employee with a record for each day of the year. Then I would look-up the Rate and Department that the Employee was working in at the given date based on the effective date. I would then have accurate cost allotment and rate calculations as Departments and Rates change during the year. Example

 

Employee Table       Department                                Rate

Emp 1                      Emp 1 - Dep1 - 1.1.2018            Emp 1 - Rate 1 - 1.1.2018

Emp 2                      Emp 2 - Dep1 - 1.1.2018            Emp 2 - Rate 1 - 1.1.2018

Emp 3                      Emp 1 - Dep2 - 1.2.2018            Emp 3 - Rate 2 - 1.1.2018

                                Emp 3 - Dep3 - 1.1.2018            Emp 1 - Rate 2 - 1.2.2018

 

Emp / Dept / Rate / Date Table

1.1.2018         Emp 1          Dep1         Rate 1

1.1.2018         Emp 2          Dep1         Rate 1       

1.1.2018         Emp 3          Dep1         Rate 2

1.2.2018         Emp 1          Dep2         Rate 2

1.2.2018         Emp 2          Dep1         Rate 1

1.2.2018         Emp 3          Dep1         Rate 2

1.3.2018         Emp 1          Dep2         Rate 2

1.3.2018         Emp 2          Dep1         Rate 1

1.3.2018         Emp 3          Dep1         Rate 2

 

Any advice for creating this with a query so that as Employees or Rates or Depts change, the new table changes as well.

 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors