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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
cseaman
Frequent Visitor

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

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

IdWorkDateEndOfPeriodDate
...8/20/20198/24/2019
...8/23/20198/24/2019
...8/29/20198/30/2019

 

EmployeeRates

EmployeeNameEffectiveDateWeeklyRate
Bill Preston1/1/2018$1000
Bill Preston8/26/2019$1100
Ted Logan2/2/2018$1200

 

EmployeeRatesByPeriod

EmployeeNamePeriodWeeklyRate
Bill Preston8/24/2019$1000
Bill Preston8/30/2019$1100
Ted Logan8/24/2019$1200
Ted Logan8/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?

1 ACCEPTED SOLUTION
cseaman
Frequent Visitor

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
)

View solution in original post

1 REPLY 1
cseaman
Frequent Visitor

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
)

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.