locked
Calculating the Sum of a Measure over rolling 6 months in PowerPivot RRS feed

  • 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

Answers

  • 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,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    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