Forum Discussion
Adding Rows and Filling in Missing Values
I am creating a compensation dashboard and running into a snag. For many of our employees, their fixed salary doesn't change since they are primarily commissioned.
| RowID | Employee ID | Eff Date | Salary |
| 1 | 1 | 12/22/2018 | 50000 |
| 2 | 1 | 12/21/2019 | 51500 |
| 3 | 1 | 12/20/2020 | 53045 |
| 4 | 2 | 12/21/2018 | 60000 |
| 5 | 2 | 12/27/2020 | 61800 |
| 6 | 3 | 12/22/2018 | 50000 |
| 7 | 3 | 12/22/2019 | 51500 |
| 8 | 3 | 12/27/2020 | 53045 |
| 9 | 4 | 12/29/2018 | 80000 |
| 10 | 5 | 12/29/2018 | 80000 |
Realistically, the data would look like this, since Employee 2 was paid 60k in 2020 (I will add a YTD and account for midyear changes as well, but one hurdle at a time).
From what I can tell, the only way to do this might be to create a static "population" table and use that to fill in the missing dates/salaries but I was really hoping to avoid that. Is there anyway this could be done dynamically?
Thanks!
Hi， adj87 ；
You could create a date table, then create a measure.
1.create a date table.
date = GENERATE(VALUES(Pay[Eff Date].[Year]),VALUES(Pay[Employee ID]))2.create a measure.
Measure = var _sum=CALCULATE(SUM('Pay'[Salary]),FILTER(ALL('Pay'),[Eff Date].[Year]=MAX('date'[Year])&&[Employee ID]=MAX('date'[Employee ID]))) var _max=CALCULATE(MAX('Pay'[Eff Date].[Year]),FILTER(ALL('Pay'),[Eff Date].[Year]<MAX('date'[Year])&&[Employee ID]=MAX('date'[Employee ID]))) return IF(_sum=BLANK(),CALCULATE(SUM([Salary]),FILTER(ALL('Pay'),[Eff Date].[Year]= _max&&[Employee ID]=MAX('date'[Employee ID]))),_sum)The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Hi， adj87 ；
You could create a date table, then create a measure.
1.create a date table.
date = GENERATE(VALUES(Pay[Eff Date].[Year]),VALUES(Pay[Employee ID]))2.create a measure.
Measure = var _sum=CALCULATE(SUM('Pay'[Salary]),FILTER(ALL('Pay'),[Eff Date].[Year]=MAX('date'[Year])&&[Employee ID]=MAX('date'[Employee ID]))) var _max=CALCULATE(MAX('Pay'[Eff Date].[Year]),FILTER(ALL('Pay'),[Eff Date].[Year]<MAX('date'[Year])&&[Employee ID]=MAX('date'[Employee ID]))) return IF(_sum=BLANK(),CALCULATE(SUM([Salary]),FILTER(ALL('Pay'),[Eff Date].[Year]= _max&&[Employee ID]=MAX('date'[Employee ID]))),_sum)The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks, this is a great start. I was able to apply this to my model and confirmed it works on the individual level. I do need this to both show individual and rolled up salary (Year/Regional).
I tried creating a seperate sum to for the individuals, but that just added up 29,120 again. I am also seeing a repeated pattern in the polulation for each year: there is a distinct count for the created date table for each ID by year (year used is Date[Year], but I get similar results from Calendar[Year] and ActiveEmployee[ID] table)
I'm unsure if this is a problem with the relationships or an issue with the measure itself
Hi， adj87 ；
This is due to the relationship is willing to cause, could you share your model relationship, or simple file?
Best Regards,
Community Support Team_ Yalan Wu
You can (and should) do this in DAX. Keep your reference table, and also have a calendar table in your data model. Then you can create a measure that compares the year in the current filter context to the reference table and picks the value that is listed for that year, or for the max year that is lower than the filtered year.
Thanks for the reply. I tried to do something like this as a new Column to see if the concept was on track and I figured I would just add "Date.Add(Pay[Year],1,YEAR)" to fill in the missing years, but I can't add this to a new column and adding a new measure results in syntax errors for Earlier(Pay[Year]), Pay[Year] and [Prorated Salary]
I think I'm missing a step on where this could actually fill down from 2018 for example to 2021. I think this could fill in 2018 to 2019, but I don't know if it could fill the gap to 2021.
I added an additional measure: "prorated salary" since many of our people do recevie salary changes (or more than 1) midyear and we need to sum those up to the total they were paid for the given year.
Any assistance would be great!
Annual Salary = var maxyear = MAX('Calendar'[Year]) var last_payment = CALCULATE(SUM([Prorated Salary]), FILTER(Pay,Pay[Year]=EARLIER(Pay[Year])-1)) return IF(Pay[Year] < maxyear,last_payment, [Prorated Salary])
