Measure needed: Find the right hourly rate

[_chris_]

Hi,

I have two tables:

1. Table hast the fields Year, CostCenter and HourlyRate

2. Table are the time bookings of my people with the fields Date, StaffId, CostCenter and Duration (=hours worked)

I want to create a measure that computes the Duration*HourlyRate. My problem is that two fields (CostCenter and the Data need to match). Also not sure if I need to create relations between these tables... 

Can someone help me with that?

Thanks, Christian

P.S.: Also I have a date table as you can see in the screenshot:

[daXtreme · Accepted solution]

/*
Say you have the following tables (use PQ to create them):

Hourly Rates / fact table, should be hidden /
------------
Hourly Rate Year | CostCenterID | Hourly Rate
-------------------------------------------------

Bookings / fact table, should be hidden /
--------
Date | StaffID | CostCenterID | Duration
----------------------------------------
Assumption is that Duration has the hour as its unit
and can't overlap two different days.

Dates / dimension /
-----
Your standard calendar table
----------------------------

Staff / dimension /
-----
StaffId | ... (and other relevant fields)

Cost Centers / dimension /
------------
CostCenterID | ... (and other relevant fields)


These can be connected like this:

'Cost Centers'[CostCenterID] 1 ->>- * 'Hourly Rates'[CostCenterID]
Dates[Year] * ->>- * 'Hourly Rates'[Hourly Rate Year]
Dates[Date] 1 ->>- * Bookings[Date]
Staff[StaffID] 1 ->>- * Bookings[StaffID]
'Cost Centers'[CostCenterID] 1 ->>- * Bookings[CostCenterID]

Some fields in the tables should be hidden. Most of all,
the key fields. I'll leave it to you to figure out which
other fields should never be exposed to the end user.

General Remark
--------------
There should never be any referential integrity problems
in your models. EVER. Slicing should never be done via
fact tables. Always via dimensions. If you don't follow
these rules of good design, be prepared for surprises
and long hair-pulling sessions.
*/

// Once you've got this, you can write the measure:

[Your Measure] =
SUMX(
    SUMMARIZE(
        Bookings,
        Dates[Year],
        'Cost Centers'[CostCenterID]
    ),
    CALCULATE(
        var CurrentYearAndCostCenterHourlyRate =
            // For any particular year and cost center
            // there should only ever be one and only
            // one entry in the 'Cost Centers' table.
            // Each combination of (Year, CostCenterID)
            // MUST have an entry for this to work.
            SELECTEDVALUE( 'Cost Centers'[Hourly Rate] )
        var Output = 
            SUM( Bookings[Duration] ) * CurrentYearAndCostCenterHourlyRate
        return
            Output
    )
)

[_chris_]

Thanks a lot for this really nice explanation. I need some time to test this and will come back to you. Have a good sunday!

[daXtreme]

Hi there.

If this has worked (and it should unless there's a typo somewhere in there), then please mark the answer as The Solution to help others.

Thanks.