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.