Using a nearest-value lookup to cross-join with some distinct dates

[cseaman]

Table TimesheetRecords has work records that also include weekly end-of-perod dates (EndOfPeriodDate), from which I want to take all distinct values, and cross-join them with an EmployeeRates table, which combines employees and their rate changes over time, and each record has an EffectiveDate for the associated rate.  What I want to do is create a DAX-generated table EmployeeRatesByPeriod which displays the cartesian product of unique periods and the applicable rate for each distinct employee during that period (below), so that I can sum the cost of an employee for a period (there's a time range slicer on my report that filters EndOfPeriodDate).  Note: I'm only using TimesheetRecords to source the weekly end dates...there's otherwise no relationship leveraged between, say, the employees who logged time and the table of employee rates.  Here's an exmple showing just the relevant fields to my problem:

TimesheetRecords

Id	WorkDate	EndOfPeriodDate
...	8/20/2019	8/24/2019
...	8/23/2019	8/24/2019
...	8/29/2019	8/30/2019

EmployeeRates

EmployeeName	EffectiveDate	WeeklyRate
Bill Preston	1/1/2018	$1000
Bill Preston	8/26/2019	$1100
Ted Logan	2/2/2018	$1200

EmployeeRatesByPeriod

EmployeeName	Period	WeeklyRate
Bill Preston	8/24/2019	$1000
Bill Preston	8/30/2019	$1100
Ted Logan	8/24/2019	$1200
Ted Logan	8/30/2019	$1200

I've looked at using VALUES, DISTINCT, or SUMMARIZE to pull the distinct EndOfPeriodDate values from TimesheetRecords, and elsewhere I've successfully used CALCULATE in a calculated column to pull the WeeklyRate using the nearest EffectiveDate.  I thought maybe I could create this table by using GENERATE, but the syntax is escaping me. I'm stuck at the non-working expression below, and here are my key issues:

1.) I know CALCULATE doesn't return a table, so it causes errors with GENERATE.

2.) I don't know the best way to include both EmployeeName and WeeklyRate fields from the EmployeeRates table.

EmployeeRatesByPeriod = GENERATE(

    DISTINCT(TimesheetRecords, TimesheetRecords[PeriodEndDate]),

    CALCULATE(
        VALUES(EmployeeRates[WeeklyRate]),
        TOPN(
            1,
            CALCULATETABLE(
                EmployeeRates,
                EmployeeRates[EffectiveDate] <= EARLIER( [PeriodEndDate] )
            ),
            EmployeeRates[EffectiveDate],
            DESC
        )
    )

)

Any recommendations on a better approach...or how to improve mine?

[cseaman · Accepted solution]

Finally figured this out - credit to Alberto Ferrari for the ultimate solution!

EmployeeRatesByPeriod =
GENERATE (
    DISTINCT ( TimesheetRecords[EndOfPeriodDate] ),
    VAR CurrentDate = TimesheetRecords[EndOfPeriodDate]
    VAR LastDateByCustomer =
        CALCULATETABLE (
            TREATAS (
                ADDCOLUMNS (
                    VALUES ( EmployeeRates[EmployeeName] ),
                    "MaxDate", CALCULATE (
                        MAX ( EmployeeRates[EffectiveDate] )
                    )
                ),
                EmployeeRates[EmployeeName],
                EmployeeRates[EffectiveDate]
            ),
            EmployeeRates[EffectiveDate] < CurrentDate
        )
    VAR RatesAtLastDate =
        CALCULATETABLE (
            EmployeeRates,
            LastDateByCustomer
        )
    RETURN
        RatesAtLastDate
)