Measure Returns Bank for Some Dates

[sean_cochran]

I am creating a historical compensation report based on two fact tables (samples below):

'Job History' (contains salary amount as of a date):

'Pay Bechmarks' (contains the date and amount of bonus payouts):

To achieve higher performance while keeping the desired filter behavior, each fact table connects to a shared date table ('Date') via a helper table that links each record to relevant dates on the calendar: 

Job History Date Key (connects 'Pay Benchmarks' to 'Dates'):

Benchmark Date Key (connects 'Pay Benchmarks' to 'Date'):

The two fact tables also share an employee name dimension, but this is not a key detail. Here is the data model: 

The measure with errors is [Bonus (AVG)]. For any given calendar date, this measure should achieve the following: a) for each employee, sum all bonuses recieved in the year of 'Date'[Date], b) return the average of these sums.

Here is the code:

Bonus (AVG) = 

VAR _maxDate = MAX('Date'[Date])

VAR _baseTable = 
    CALCULATETABLE(
            SELECTCOLUMNS(
                'Job History',
                "Employee",CONCATENATE([Employee],""),
                "Base",[Base Wage]
            ),
            'Job History Date Key'[Dates] = _maxDate
    )

VAR _bonusTable = 
    SELECTCOLUMNS(
        GROUPBY(
            FILTER(
                'Pay Benchmarks',
                YEAR('Pay Benchmarks'[Date]) = YEAR(_maxDate)
            ),
            [Employee],
            "Bonus",SUMX(CURRENTGROUP(),[Bonus])
        ),
        "Employee",CONCATENATE([Employee],""),
        "Bonus",[Bonus]
    )

VAR _TCCtable = 

    ADDCOLUMNS(
        NATURALLEFTOUTERJOIN(_baseTable,_bonusTable),
        "Date",_maxDate
    )

RETURN AVERAGEX(_TCCtable,[Bonus])

Currently, this measure fails to return a result for individuals on dates before a benchmark occurred. For example, Jill (200) had a benchmark record on July 15, 2020. Her bonus should show up for every day in the year 2020, but it only shows up on or after July 15:

This behavior makes me think that this filter is not working correctly:

FILTER(
'Pay Benchmarks',
YEAR('Pay Benchmarks'[Date]) = YEAR(_maxDate)
)

However, when I run this similar code in DAX Studio, it appears to work as expected:

EVALUATE

FILTER(
ADDCOLUMNS(
    Date,
    "Bonus",
        VAR _testDate = [Date]
        VAR _bonusTable = 
            GROUPBY(
                FILTER(
                    'Pay Benchmarks',
                    YEAR([Date]) = YEAR(_testDate)
                ),
                [Employee],
                "Bonus",SUMX(CURRENTGROUP(),[Bonus])
            )
        VAR _finalTable = FILTER(_bonusTable,[Employee] = "200")
    RETURN SUMX(_finalTable,[Bonus])
	),[Date] > DATE(2020,07,10)
)

Here is the output from runing this code in DAX studio:

Click here to download the file for testing.

Is there something I'm missing?

[sean_cochran · Accepted solution]

I believe I solved my own problem.

The relationship between 'Pay Benchmarks' and 'Benchmark Key Date' was filtering out data from my measure. To get around this, I wrapped the _bonusTable variable code in a CALCULATETABLE function and used CROSSFILTER to deactivate that relationship for this measure.

The final working measure is as follows:

Bonus (AVG) = 

VAR _maxDate = MAX('Date'[Date])

VAR _baseTable = 
    CALCULATETABLE(
            SELECTCOLUMNS(
                'Job History',
                "Employee",CONCATENATE([Employee],""),
                "Base",[Base Wage]
            ),
            'Job History Date Key'[Dates] = _maxDate
    )

VAR _bonusTable = 
    
CALCULATETABLE(    
    SELECTCOLUMNS(
        GROUPBY(
            FILTER(
                'Pay Benchmarks',
                YEAR('Pay Benchmarks'[Date]) = YEAR(_maxDate)
            ),
            [Employee],
            "Bonus",SUMX(CURRENTGROUP(),[Bonus])
        ),
        "Employee",CONCATENATE([Employee],""),
        "Bonus",[Bonus]
    ),
    CROSSFILTER('Pay Benchmarks'[benchmarkUID],'Benchmark Date Key'[benchmarkUID],None))

VAR _TCCtable = 

    ADDCOLUMNS(
        NATURALLEFTOUTERJOIN(_baseTable,_bonusTable),
        "Date",_maxDate
    )

RETURN AVERAGEX(_TCCtable,[Bonus])

Results in Power BI Desktop after making the change: