topic Calculate salary between dates in DAX Commands and Tips

Calculate salary between dates

Anonymous — Fri, 04 Feb 2022 15:03:46 GMT

I'm trying to write a measure to return the salary paid to an employee between dates selected by a slicer. Employees could have more than one salary between those dates.

I have a table 'Salary History' with columns for the start date of the salary [Effective Date] and end date [End Date], as well as the salary per day [Daily Salary]

There is a row for each salary and there can be many rows for one employee [Employee ID]

I also have a date table with the boolean column [Is Weekday] so I can calculate working days between two dates.

I have made a column showing the pay between the effective and end date of the salaries:

Pay during effective period =

CALCULATE(COUNTROWS ('Date'),

DATESBETWEEN('Date'[Date], 'Salary History'[Effective Date],

IF(ISBLANK('Salary History'[End Date]), TODAY(), 'Salary History'[End Date])),

'Date'[Is WeekDay] = TRUE)

* [Daily Salary]

which seems to work.

I then tried this for the measure:

SALARY during period =

VAR periodStart =

IF('Salary History'[Effective Date]<=LASTDATE('Date'[Date]),

IF('Salary History'[Effective Date]>=FIRSTDATE('Date'[Date]), 'Salary History'[Effective Date], FIRSTDATE('Date'[Date])))

VAR periodEnd =

IF(OR(ISBLANK('Salary History'[End Date]), 'Salary History'[End Date]>=FIRSTDATE('Date'[Date])),

IF(ISBLANK('Salary History'[End Date]), LASTDATE('Date'[Date]),

IF('Salary History'[End Date]<=LASTDATE('Date'[Date]), 'Salary History'[End Date], LASTDATE('Date'[Date]))))

RETURN

CALCULATE(

COUNTROWS ('Date'),

DATESBETWEEN('Date'[Date], periodStart, periodEnd), 'Date'[Is WeekDay] = TRUE)

* 'Salary History'[Daily Salary]

but i got the error: A single value for column 'Effective Date' in table 'Salary History' cannot be determined.

I tried adding SELECTEDVALUE before the effective dates and this removed the error, but when I tried to add the measure to a table it woulndn't load saying it had run out of memory.

I'd appreciate any help!

(edited to try and make the code a bit clearer)

Re: Calculate salary between dates

amitchandak — Fri, 04 Feb 2022 13:48:46 GMT

Anonymous , if you want to use that you need to use max.

I think you need move that to filter clause

IF(max('Salary History'[Effective Date])<=LASTDATE('Date'[Date]), IF(max('Salary History'[Effective Date])>=FIRSTDATE('Date'[Date]), 'Salary History'[Effective Date], FIRSTDATE('Date'[Date])))
VAR periodEnd = IF(OR(ISBLANK(max('Salary History'[End Date])), max('Salary History'[End Date])>=FIRSTDATE('Date'[Date])), IF(ISBLANK(max('Salary History'[End Date])), LASTDATE('Date'[Date]), IF(max('Salary History'[End Date])<=LASTDATE('Date'[Date]),max('Salary History'[End Date]), LASTDATE('Date'[Date]))))

Example filter , check current employee

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Re: Calculate salary between dates

Anonymous — Fri, 04 Feb 2022 16:06:36 GMT

Thanks for replying. Unfortunately I get the same performance issue as when I tried SELECTEDVALUE. I think I need to find a different way to filter the dates.

Re: Calculate salary between dates

AlexisOlson — Fri, 04 Feb 2022 16:55:55 GMT

These look like definitions for calculated columns rather than measures.

I think I'd try something like this:

SALARY during period = VAR periodStart = MAX ( MIN ( 'Salary History'[Effective Date] ), MIN ( 'Date'[Date] ) ) VAR periodEnd = MIN ( MAX ( 'Salary History'[Effective Date] ), MAX ( 'Date'[Date] ) ) VAR daysInPeriod = CALCULATE ( COUNTROWS ( 'Date' ), DATESBETWEEN ( 'Date'[Date], periodStart, periodEnd ), 'Date'[Is WeekDay] = TRUE ) RETURN daysInPeriod * [Daily Salary]