Forum Discussion

MojoGene's avatar
MojoGene
Post Patron
10 years ago
Solved

LOOKUPVALUE help

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 [Timekee...
Need Help
  • Sean's avatar
    Sean
    10 years ago

    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's avatar
MojoGene
Post Patron
10 years ago

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.

Nhallquist's avatar
Nhallquist
Helper V
10 years ago

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's avatar
    MojoGene
    Post Patron
    10 years ago

    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