Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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?
Solved! Go to 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
)
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
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
21 | |
17 | |
16 | |
11 | |
7 |
User | Count |
---|---|
26 | |
25 | |
12 | |
12 | |
12 |