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.

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.