Calculate salary between dates

[Anonymous]

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)

[AlexisOlson · Accepted solution]

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]

[amitchandak]

@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

[Anonymous]

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.