Forum Discussion

MojoGene's avatar
MojoGene
Icon for Post Patron rankPost 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!

     

mike_honey's avatar
mike_honey
Icon for Memorable Member rankMemorable Member
10 years ago

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] ) )