title: Measure as date variable when calculating time periods in Powerpivot
body: <p>My situation is as following:</p>
  
  <p>This is a Powerpivot solution developed in Excel 2013 (32-bit).
  I got a transaction table containing transactions with an amount, a category and a posting date. What I would like to to is to present a number of different calculations depending on the time frame.</p>
  
  <ul>
  <li>Sum of amount of current day of import (all the transations with the latest posting date available).</li>
  <li>Sum of amount Month-to-date (the current month of the latest transaction)</li>
  <li>Sum of amount same period last month (Month-to-date minus one month)</li>
  <li>Sum of amount last month (the totals for whole last month)</li>
  </ul>
  
  <p>So, idea is to create a "Current day" measure as a stand point for all the other measures.</p>
  
  <pre><code>[Current day] = LASTDATE('TransactionTable'[Posting Date])
  </code></pre>
  
  <p>Before summarizing things I wanted to create measures that would represent the start and end date for each period (to display in the report and to make easier measures), this is where I run into trouble.</p>
  
  <pre><code>[First day of current month] = STARTOFMONTH([Current Day])
  </code></pre>
  
  <p>Gives me the error: <em>"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."</em></p>
  
  <p><strong>And with this I would like to end up with something like this for current month:</strong></p>
  
  <pre><code>[Sum of amount current month] = CALCULATE(SUM('Transactiontable'[Amount]);DATESBETWEEN('DateTable'[Date]; [First day of current month];[Current day]))
  </code></pre>
  
  <p><strong>And this for previous month total:</strong></p>
  
  <pre><code>[First day of previous month] = DATEADD([First day of current month];-1;MONTH)
  [Last day of previous month] = EOMONTH([Current day];-1)
  
  CALCULATE(SUM('Transactiontable'[Amount]);DATESBETWEEN('DateTable'[Date]; [First day of previous month]; [Last day of previous month]))
  </code></pre>
  
  <p>It feels like I am not using the measures the "right" way... Basically I want to create dynamic measures that will change the timeframe depending on what the latest posting date is in the transaction table. Is this the way to go at all?</p>
  
  <p>Thanks guys,</p>