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

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