LOOKUPVALUE help

[MojoGene]

I am attempting to determine timekeeper net profit by subtracting the timekeeper's costs from the value of the timekeeper's work.

There is a table, 'TimeTable', that contains columns for [Timekeeper], [Hours], [Billing Rate], etc.

In the TimeTable, there is a column [Hourly Value] that is the product of [Hours]*[Billing Rate].

I also have a measure called [YTD Value]: YTD Value = TOTALYTD(SUM(TimeTable[Hourly Value]), Table_BasicCalendarUS[DateKey], "12/31")

Annual costs for each timekeeper are set forth in a simple lookup table called 'Costs', which has only two columns, one colum for [Timekeeper] and one column for total annual costs, [Annual Cost].

I want to determine YTD costs in this situation by doing a lookup on the Costs table for [Annual Cost] on each Timekeeper and then multiply the annual costs by the year fraction. (I have a column in DimDate called [Calendar Year Fraction].)

So, my measure for profit looks like this:

YTD Profit = MAX(TimeTable[YTD Value]) - LOOKUPVALUE(Costs[TotalComp ], Costs[Timekeeper],TimeTable[Timekeeper]) * DimDate[Calendar Year Fraction]

But this returns the following error: "Column 'YTD Value' in table 'TimeTable' cannot be found or may not be used in this expression."

How would I putting this together?

Thanks.

[mike_honey]

I think it's best to avoid LOOKUPVALUE in Measures - it works better in Calculated Columns.

I would add a Relationship between TimeTable and Costs (on TimeKeeper) and between TimeTable and DimDate (assuming TimeTable has a Date/DateKey column).

Then the measure would look something like:

YTD Profit = SUM(TimeTable[YTD Value]) - ( SUM(Costs[TotalComp ] * SUM ( DimDate[Calendar Year Fraction] ) )

[MojoGene]

Mike:

Thanks for taking a stab at this. Unfortunately, I am still running into a brick wall.

The relationships you suggested already existed. 

The measure you suggested returned the following error: "Column 'TotalComp' in table 'Costs' cannot be found or may not be used in this expression."

Any suggestions on the solution here would be appreciated.

[mike_honey]

You code had a space between TotalComp and ], which I copied.  That mightve been a typo in your OP.  Intellisense should make this easy to resolve at your end.

[Sean · Accepted solution]

@MojoGene These are the Measures I used ....

YTD Value = TOTALYTD(SUM(TimeTable[Hourly Value]), CalendarTable[Date])

YTD Cost = SUM(Costs[Total Comp])*YEARFRAC(STARTOFYEAR(CalendarTable[Date]), TODAY(),3)

YTD P/L = [YTD Value] - [YTD Cost]

YTD P/L % = DIVIDE([YTD P/L], [YTD Cost], 0)

Here's the sample I created and the result... Hope this helps!

[MojoGene]

Sean:

Eurika!

Thanks very much.

Gene

[Nhallquist]

@mike_honey is right, you want to avoid LOOKUPs.  In the Relationships screen of PBI, can you verify that the relationship between Cost Table and Time Table is active?  Also, you should look to make sure that there is a Many to One (Many in Time, One in Cost) Relationship created.  If the relationship is not active, or it is a Many to Many, you will have issues.  If the Cost table has multiple entries for each TimeKeeper, you should be able to edit the data query to only bring the max value for each TimeKeeper.

Let us know the results.

Nate

[MojoGene]

Nate: 

Thanks for the assistance.

I confirmed that there is a Many-to-One relationship between TimeTable and Cost. (There is only one entry for each Timekeeper in the Cost table.) Still cannot find my way through the weeds here.

I was thinking that maybe I could approach this in two steps, first by creating a column in the Costs table for the [YTD Cost]. (There is no data dimension in this table, but logically there seems to be no reason why this cannot be calculated by multiplying [Annual Cost] by YEARFRAC? I have tried this:

=YEARFRAC(Date(Year(Now()),1,1),3) * [Annual Cost])

Unfortunately, this returns wildly inaccurate results.

If I can get that to work, the next step would seem to be to calculate [YTD Value] - [YTD Cost].

Thanks again for any advice.

Gene