Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Start and End Dates on Report

    Posted 30 days ago
    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
    ------------------------------


  • 2.  RE: Start and End Dates on Report

    Posted 29 days ago
    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
    ------------------------------



  • 3.  RE: Start and End Dates on Report

    Top Contributor
    Posted 29 days ago
    Edited by Sam Duval 29 days ago
    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
    ------------------------------



  • 4.  RE: Start and End Dates on Report

    Posted 29 days ago
      |   view attached
    rrrrrrr





  • 5.  RE: Start and End Dates on Report

    Posted 29 days ago
      |   view attached
    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
    ------------------------------

    Attachment(s)