Forum Discussion

adj87's avatar
adj87
Helper I
4 years ago
Solved

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...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

lbendlin's avatar
lbendlin
Super User
4 years ago

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's avatar
    adj87
    Helper I
    4 years ago

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