Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Yggdrasill
Responsive Resident
Responsive Resident

Find value between two tables and dates

Maybe it's because it's monday or something but I just can't get my head around a what I thought was a simple stuff but here I am, few hours later...

Problem:

I have two tables with no active relationship

First table is my fact table which holds all data related to punched hours

FactTable

DateHoursEmployeeId
29.12.20208A

30.12.2020

8A

31.12.2020

4A

1.1.2021

4A

2.1.2021

8A


My second table has information on salary rates per employee with a given start date like so:

SalaryTable

EmployeeIdSalaryFromDateRate
A1.1.202050
A1.1.2021100
B1.1.200960

 

My desired result

Using Dax measure I want to calculate the cost for each hours that is actually punched in and worked.

I'm trying to use this for example

 

Rate_Hourly =
VAR _salaryfromdate =
    SELECTEDVALUE ( SalaryTable[SalaryTableFromDate] )
RETURN
    CALCULATE (
        SUM ( SalaryTable[hourlySalaryTable] ),
        FILTER (
            FactTable,
            FactTable[date] >= _salaryfromdate
                && FactTable[date] <= TODAY ()
        )
    )

 

But this just gives me sum of all Hour rates.


How can I make sure I find the correct Hour rate within the date range given from FactTable and compare to SalaryTable?

 

Kindest

 

5 REPLIES 5
sevenhills
Super User
Super User

I tried this and see if it works for you

 

sevenhills_0-1634594016995.png

 

sevenhills_1-1634594031618.png

 

No relationships created

sevenhills_2-1634594051064.png

Measure

Rate_Hourly =  
var _Rate = MAXX( 
    FILTER( SalaryTable, SalaryTable[EmployeeID] = SELECTEDVALUE(FactTable[EmployeeID])
                     && SalaryTable[SalaryFromDate] <= SELECTEDVALUE(FactTable[Date]))
                     , SalaryTable[Rate]
) 
return _Rate

 

FYI: I prefer to have a calculated column than measure if it is a large dataset

sevenhills_4-1634594180712.png

 

... 

@sevenhills Thanks for the prompt reply and taking the time to help out!

I've successfully implemented the DAX code and it works. However it breaks at another granular level when I'm not looking at dates. See how the total does not sum up

Yggdrasill_0-1634745936669.png


Can I somehow make this work without having to use ISINSCOPE() or other similar functions?

Or should I just do as you mentioned and use a column?

Kind regards

Please can you provide what is expected output in your top scenario (like a mockup)

 

Tough to visualize sometimes ... 

The expected output is to find hourly rate and multiply that number with the amount of hours worked. That should define the cost. However I need to be able to look at cost regardless of if I'm looking at one employee, two or all.


 

Come to think of it maybe it's easier to let PQ calculate the cost in the fact table? So everytime an hour is registered another column is calculated for the cost behind that hour

 

If my analysis of your problem is wrong, then I need more details.

 

Optionally, you can take a look 

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.