LOOKUP measure returning error

[MojoGene]

I am running into a dead end and hope to get some help …

I am trying to create a measure to determine the Hourly Profit from a timekeeper’s work. My first table “TimeTable” consists of columns for [Timekeeper], [Hours], [Hourly Billing Rate], and other columns. There is a measure that determines the 3-month moving average for monthly hours for each timekeeper called TimeTable[Hours 3-Month Moving Ave].

I have a second, “lookup” table “Lookup”, which ties to the TimeTable table with the column Lookup[Emp] = TimeTable[Timekeeper]. In the Lookup table are other columns including one for the monthly timekeeper overhead costs (salary, benefits, etc.), entitled Lookup[MonthlyOCost].

So, logically, the measure evaluates this: ([Hours] * [Hourly Billing Rate]) - ([MonthlyOCost]/[Hours 3-Month Moving Ave])*[Hours].

I have been trying different approaches. The current iteration of the measure looks like this:

Hourly Profit = (TimeTable[Hours] * TimeTable[Hourly Billing Rate]) - LOOKUPVALUE(Lookup[MonthlyOCost],Lookup[Emp],TimeTable[Timekeeper]) / CALCULATE(TimeTable[Hours 3-Month Moving Ave],TimeTable[Timekeeper],DateTable[MonthOfYear],-1)

When this measure is evaluated, it returns this error: “The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.”

Any assistance appreciated!

[v-caliao-msft]

Hi MojoGene,

From your DAX
Hourly Profit = (TimeTable[Hours] * TimeTable[Hourly Billing Rate]) - LOOKUPVALUE(Lookup[MonthlyOCost],Lookup[Emp],TimeTable[Timekeeper]) / CALCULATE(TimeTable[Hours 3-Month Moving Ave],TimeTable[Timekeeper],DateTable[MonthOfYear],-1)
It's seems that syntax of LOOKUPVALUE funtion is correct. So as per my understanding, the issue could cause by the Calculate function.

Please elaborate your issue and provide us some sample data, so that we can make further analysis.

Regards,

Charlie Liao

[MojoGene]

Thanks for the reply.

The purpose of the "-1" is to make sure that the three month running average for monthly hours is based on the prior month, for which (presumably) the time input will be finished, rather than the current month. (Workers, unfortunately, are late handing in time sheets on a daily basis, so the current monthly hours are not completely entered until after the start of the following month.)

In any event, I tried to run the measure without the "-1" and got this error returned: "Cannot convert value 'XXX' of type Text to type True/False" (where XXX are the employee's initials in TimeTable[Timekeeper]).

I tried using the DIVIDE function and got the same error:

Hourly Profit = (TimeTable[Hours] * TimeTable[Hourly Billing Rate]) - DIVIDE(LOOKUPVALUE(Lookup[MonthlyOCost],Lookup[Emp],TimeTable[Timekeeper]), CALCULATE(TimeTable[Hours 3-Month Moving Ave],TimeTable[Timekeeper],DateTable[MonthOfYear]))

[waltheed]

The error you are getting is something else, has nothing to do with the -1.

You may get to the cause by splitting up your DAX expression into smaller pieces. On step at a time 🙂

The -1 error is gone now. You may be able to achieve the -1 trick by changing the DateTable[MonthOfYear] filter in the CALCULATE expression to return the previous month, e.g.

FILTER( STARTOFMONTH(DateTable[Date]) = date(year(TODAY()), MONTH(today()) -1, 1) )

Hope this works...

[waltheed]

Hi MojoGene,

The -1 at the end of the CALCULATE function is what gives this error.

I am not sure what you want to filter by, using the -1. 

By the way, instead of dividing values with / it is safer to use the DIVIDE function.

HTH,