Power BI Exchange

Hi Team,

I'm trying to create a PowerBi report for the Labor Costs, the problem I face is that my company has constant changes in the pay rates of the employees. I was thinking of making a log for the Pay rates for the employees with start and end dates to have the effective dates to accurately account for their actual Pay Rate that was active at the date of the hours worked.

The problem is that I don't know how to make the start and end dates communicate to the other information. Any suggestions?

------------------------------
David A. Jimenez
Business Financial Analyst
------------------------------

Hi David,
My opinion is this should be set up at data warehouse. where you have employee work schedule and basic rate. Both must have date time field. After that You can make relationship with date dimension

------------------------------
Dili Timor
Freelance Power BI Dev
------------------------------

Original Message:
Sent: Jun 01, 2022 05:37 PM
From: David Jimenez
Subject: Start and End Dates on Report

Hi Team,

I'm trying to create a PowerBi report for the Labor Costs, the problem I face is that my company has constant changes in the pay rates of the employees. I was thinking of making a log for the Pay rates for the employees with start and end dates to have the effective dates to accurately account for their actual Pay Rate that was active at the date of the hours worked.

The problem is that I don't know how to make the start and end dates communicate to the other information. Any suggestions?

------------------------------
David A. Jimenez
Business Financial Analyst
------------------------------

Assuming you mean communicate as in "joining" it to the other tables. this is a little trickier since power bi really only allows you to join on one column, you might be able to pull it off with a secondary inactive relationship, but not positive. I think what you'll need to do add a column via DAX as shown in this thread. This is done in the power BI desktop editor (not power query)
https://community.powerbi.com/t5/Desktop/conditional-join/m-p/339960


------------------------------
Sam Duval
Data Quality Analyst
Element Financial
Indianapolis IN
------------------------------

Original Message:
Sent: Jun 01, 2022 05:37 PM
From: David Jimenez
Subject: Start and End Dates on Report

Hi Team,

I'm trying to create a PowerBi report for the Labor Costs, the problem I face is that my company has constant changes in the pay rates of the employees. I was thinking of making a log for the Pay rates for the employees with start and end dates to have the effective dates to accurately account for their actual Pay Rate that was active at the date of the hours worked.

The problem is that I don't know how to make the start and end dates communicate to the other information. Any suggestions?

------------------------------
David A. Jimenez
Business Financial Analyst
------------------------------

Original Message:
Sent: 6/2/2022 6:49:00 AM
From: Sam Duval
Subject: RE: Start and End Dates on Report

Assuming you mean communicate as in "joining" it to the other tables. this is a little trickier since power bi really only allows you to join on one column, you might be able to pull it off with a secondary inactive relationship, but not positive. I think what you'll need to do add a column via DAX as shown in this thread. This is done in the power BI desktop editor (not power query)
https://community.powerbi.com/t5/Desktop/conditional-join/m-p/339960


------------------------------
Sam Duval
Data Quality Analyst
Element Financial
Indianapolis IN
------------------------------

Original Message:
Sent: Jun 01, 2022 05:37 PM
From: David Jimenez
Subject: Start and End Dates on Report

Hi Team,

I'm trying to create a PowerBi report for the Labor Costs, the problem I face is that my company has constant changes in the pay rates of the employees. I was thinking of making a log for the Pay rates for the employees with start and end dates to have the effective dates to accurately account for their actual Pay Rate that was active at the date of the hours worked.

The problem is that I don't know how to make the start and end dates communicate to the other information. Any suggestions?

------------------------------
David A. Jimenez
Business Financial Analyst
------------------------------

I had a very similar problem; with a little tweaking it may work for you...
In my situation we needed to show all contract details that were active for any given point in time - similar to your changing pay rates.

To do this, I did NOT create a relationship between the contracts table and date table - doing this created issues and was getting extremely complicated with the DAX UseRelationship function and constantly flipping the date relationship back and forth. I put the date filter into a Rate measure calculation. This filtered the Contract Table based on the date selected.

Rate = CALCULATE (
MAX( Contract[Rate] ),
FILTER ( Contract, Contract[end_date] >= MAX ('Date'[Date] ) ) ,
FILTER ( Contract, Contract[start_date] <= MIN ('Date'[Date] ) )
)

Similar to the solution from @Sam Duval

Worked for me, but there may be a better way ...
(pbix example attached) ​​​

------------------------------
Bob Parsons
Business Intelligence Manager
Star Produce
------------------------------

Original Message:
Sent: Jun 01, 2022 05:37 PM
From: David Jimenez
Subject: Start and End Dates on Report

Hi Team,

I'm trying to create a PowerBi report for the Labor Costs, the problem I face is that my company has constant changes in the pay rates of the employees. I was thinking of making a log for the Pay rates for the employees with start and end dates to have the effective dates to accurately account for their actual Pay Rate that was active at the date of the hours worked.

The problem is that I don't know how to make the start and end dates communicate to the other information. Any suggestions?

------------------------------
David A. Jimenez
Business Financial Analyst
------------------------------