Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate salary between dates

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's avatar
    AlexisOlson
    4 years ago

    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]

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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]