Incorrect DAX Measure Totals in Table Visual

[evgeniam]

Hi Power BI Experts!

I am calculating Revenue per Employee as the sum of revenue time entries in the database. In cases when employees leave the company, we want to discontinue adding their time to the revenue total. 

I used the following measure to account for theat:

New Resource Rate*Hours =

var termination_Date = MAX(Employees[TerminationDate])

RETURN

CALCULATE(SUM(RevenueDetail[RateTimesHours]),

FILTER(RevenueDetail, RevenueDetail[Date]<=termination_Date))

It works, but the total is incorrect. I tried some of the solutions suggested to others dealing with similar issues, but no luck. 

Total Rate*Hours =

var termination_Date = MAX(Employee[TerminationDate])

RETURN

SUMX(

VALUES( RevenueDetail[RateTimesHours]),

IF(RevenueDetail[Date]<=termination_Date,

CALCULATE(SUM(RevenueDetail[RateTimesHours]))))

This returns an error "A single value for column "Date" cannot be determined..."

I appreciate your help with this!!

[ImkeF · Accepted solution]

Hi @evgeniam 

I think that something along this line should get you going:

Measure2 =
SUMX (
    FILTER (
        'RevenueDetail',
        'RevenueDetail'[Date] <= CALCULATE ( MAX ( 'Employee'[TerminationDate] ) )
            || ISBLANK ( CALCULATE ( MAX ( 'Employee'[TerminationDate] ) ) )
    ),
    'RevenueDetail'[RateTimesHours]
)

The MAX(TerminationDate) has to be evaluated on a row-by-row-basis for the Total to calculate correctly.

[evgeniam]

@ImkeF ,

This works beautifully. Thank you!!!