Adding Rows and Filling in Missing Values

[adj87]

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!

[v-yalanwu-msft · Accepted solution]

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.

[adj87]

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

[v-yalanwu-msft]

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

[adj87]

I figured out my issue now. I was using my Calendar table instead of the created date table ID/Year reference. I just needed to add my commission/bonus totals with the correct relationship

[adj87]

Here is my current relationship model... I will work on getting a simplified file. Everything is either linked from ID to Active table to Pay Date to Calendar table

I was unsure how to handle the created table (I called 'YearDate').

[lbendlin]

You would use the new table in a slicer, a crossjoin, or both.

Nice clean data model.

[lbendlin]

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.

[adj87]

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])