Find value between two tables and dates

Question

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 relationshipFirst table is my fact table which holds all data related to punched hoursFactTableDateHoursEmployeeId29.12.20208A30.12.20208A31.12.20204A1.1.20214A2.1.20218AMy second table has information on salary rates per employee with a given start date like so:SalaryTableEmployeeIdSalaryFromDateRateA1.1.202050A1.1.2021100B1.1.200960&nbsp;My desired resultUsing&nbsp;Dax measure&nbsp;I want to calculate the cost for each hours that is actually punched in and worked.I'm trying to use this for example&nbsp;Rate_Hourly =
VAR _salaryfromdate =
    SELECTEDVALUE ( SalaryTable[SalaryTableFromDate] )
RETURN
    CALCULATE (
        SUM ( SalaryTable[hourlySalaryTable] ),
        FILTER (
            FactTable,
            FactTable[date] &gt;= _salaryfromdate
                &amp;&amp; FactTable[date] &lt;= TODAY ()
        )
    )&nbsp;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?&nbsp;Kindest&nbsp;

sevenhills · Answer

I tried this and see if it works for you&nbsp;&nbsp;&nbsp;No relationships createdMeasureRate_Hourly =  
var _Rate = MAXX( 
    FILTER( SalaryTable, SalaryTable[EmployeeID] = SELECTEDVALUE(FactTable[EmployeeID])
                     &amp;&amp; SalaryTable[SalaryFromDate] &lt;= SELECTEDVALUE(FactTable[Date]))
                     , SalaryTable[Rate]
) 
return _Rate&nbsp;FYI: I prefer to have a calculated column than measure if it is a large dataset&nbsp;...&nbsp;

yggdrasill · Answer

sevenhills&nbsp;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 upCan 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

sevenhills · Answer

Please can you provide what is expected output in your top scenario (like a mockup)&nbsp;Tough to visualize sometimes ...&nbsp;

yggdrasill · Answer

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.&nbsp;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

sevenhills · Answer

DAX I provided the measure is from your original post and based on a date.What you are asking is SCD2 - Fact type of example.Let me explain, in case you have not noted already as this type.You have two tablesOne table is fact i.e., containing transactions. in your case, employee worked hours on each date.Second table is SCD i.e., Slowly change dimension. Where you have effective start date and rate for each employee. (and you are missing is effective end date, which is not an issue).What you are looking is to get the rate based on the transaction date!You can take a look at these examples and fit to your needs.https://p3adaptive.com/2019/06/how-can-i-get-a-lookup-table-from-a-slowly-changing-dimension-scd/https://www.zartis.com/scd-implementation-with-temporal-tables-in-power-bi/&nbsp;If my analysis of your problem is wrong, then I need more details.&nbsp;Optionally, you can take a look&nbsp;https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970&nbsp;&nbsp;

Forum Discussion

Find value between two tables and dates

5 Replies

Related content

Formatting Value of Table

PowerBI - Matrix table, hide values used only for a visual calculation

Table Call Value Formatting Options

Parsing list values in a nested table

SUMMARIZE, VALUES, and other table generation/manipuation with field parameters

Date	Hours	EmployeeId
29.12.2020	8	A
30.12.2020	8	A
31.12.2020	4	A
1.1.2021	4	A
2.1.2021	8	A

EmployeeId	SalaryFromDate	Rate
A	1.1.2020	50
A	1.1.2021	100
B	1.1.2009	60