# Calculating the Sum of a Measure over rolling 6 months in PowerPivot

• ### Question

• I am trying to calculate the sum of gross sales over a rolling 6 month period in PowerPivot and don't even know how to start.

Monday, May 4, 2015 6:13 PM

• I tried this

=IF(ISBLANK(sum([Gross Sales USD])),BLANK(),

CALCULATE(sum([Gross Sales USD]),

DATESINPERIOD([Month Year Name],

LASTDATE([Month Year Name]),-6,MONTH))

but it didn't work I have to put a month name?  my time field Month Year name is in dd/mm/yyyy format. but if I put in a month name wont it be static?  I need to always look for the last 6 months from now but be dynamic into the future as well

Monday, May 4, 2015 11:59 PM

### All replies

• HI Ball in da Bay,

You can use the approach described here. If you read through the article and feel that you require some assistance in applying the approach to your specific scenario, please provide details of your model as described here so that a member of the forum or myself can help.

Regards,

Monday, May 4, 2015 6:33 PM
• I tried this

=IF(ISBLANK(sum([Gross Sales USD])),BLANK(),

CALCULATE(sum([Gross Sales USD]),

DATESINPERIOD([Month Year Name],

LASTDATE([Month Year Name]),-6,MONTH))

but it didn't work I have to put a month name?  my time field Month Year name is in dd/mm/yyyy format. but if I put in a month name wont it be static?  I need to always look for the last 6 months from now but be dynamic into the future as well

Monday, May 4, 2015 11:59 PM