title: Alternative to LOOKUPVALUE function for use within a measure?
body: <p>I have a measure that successfully calculates a cumulative amount of planned revenue through each month:</p>
  
  <pre><code>Revised Revenue :=
  CALCULATE (
      SUM ( Rev_Revised[Revenue] ),
      Data_Types[Data Types] = "Projected"
  )
  </code></pre>
  
  <pre><code>Cumulative Revised Revenue :=
  IF (
      MIN ( MonthDate[MonthDate] )
          &lt;= CALCULATE ( MAX ( Rev_Revised[MonthDate] ), ALL ( Rev_Revised ) ),
      CALCULATE (
          [Revised Revenue],
          FILTER (
              ALL ( MonthDate[MonthDate] ),
              MonthDate[MonthDate] &lt;= MAX ( MonthDate[MonthDate] )
          )
      )
  )
  </code></pre>
  
  <p>When used in a pivot table in conjunction with Running Total In..., it looks like this:</p>
  
  <p><a href="https://i.stack.imgur.com/F6PFC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/F6PFC.png" alt="image 1"></a></p>
  
  <p>What I need to do now is create a new measure that multiplies a Revenue Capture % to each monthly cumulative amount (this is like a prediction on the lag of actually collecting the revenue), and those percentages are found in a related table:</p>
  
  <p><a href="https://i.stack.imgur.com/R4xyE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/R4xyE.png" alt="image 2"></a></p>
  
  <p>Using <code>LOOKUPVALUE</code> in a measure obviously throws an error because it doesn't result in one value.  I could easily just create my own additional row under the pivot table that performs this calculation, but I'd really like it to be performed within the data model.  Any ideas?</p>
  
  <p>Edit:
  The closest solution I can come to now is a calculated column in my spend plan dataset that looks up the % for each row:</p>
  
  <pre><code>Monthly Rev Capture :=
  LOOKUPVALUE (
      Rev_Capture[Revenue Capture],
      Rev_Capture[MonthDate], Rev_Revised[MonthDate]
  )
  </code></pre>
  
  <p>And then a measure of this:</p>
  
  <pre><code>Rev Capture Amount :=
  [Cumulative Revised Revenue] * AVERAGE ( Rev_Revised[Monthly Rev Capture] )
  </code></pre>
  
  <p>But that doesn't calculate correctly in the pivot table.  Here's what it looks like, also with the correct manual calculations displayed below the table for validation:</p>
  
  <p><a href="https://i.stack.imgur.com/zT0sM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zT0sM.png" alt="enter image description here"></a></p>
  
  <p>What I need is a measure similar to one of these, but of course these fail:</p>
  
  <pre><code>Rev Capture Total:=
  [Cumulative Revised Revenue]*RELATED(Rev_Capture[Revenue Capture])
  </code></pre>
  
  <pre><code>Rev Capture Total v2:=
       [Cumulative Revised Revenue]*LOOKUPVALUE(Rev_Capture[Revenue Capture],
  MonthDate[MonthDate],
  Rev_Capture[MonthDate])
  </code></pre>