Get the value of a measure of the last x intervals from the current datetime with PowerPivot

By | May 7, 2010

A quick post about a handy dax formula that gives me the sum of a measure of the last x periods using the current time.

The key is using the datesinperiod function in combination with the NOW() function. We can use this function to get a list of all the dates from our fact table that falls in the period defined in the function. You can define a start date and a number of intervals and what intervals to use.

When i would like the sum of the total salesamount from the last month i would create the following formula:

=CALCULATE(sum(FactTable[SalesAmount]), DATESINPERIOD(‘Date'[Datekey],NOW(),-1,MONTH))

This will return the sum of total salesamount from all the rows from the  facttable between NOW() and NOW() – 1 MONTH.

One thing to keep in mind, the NOW() will have the datetime of the moment that you create the measure or the moment that you refresh your PowerPivot data.