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.

adj87's avatar
adj87
Helper I
4 years ago

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's avatar
v-yalanwu-msft
Community Support
4 years ago

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

    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's avatar
      lbendlin
      Super User
      4 years ago

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

       

      Nice clean data model.

  • adj87's avatar
    adj87
    Helper I
    4 years ago

    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