locked
Last 3 month average sell amount RRS feed

  • Question

  • Dear All,

    I am making a report for showing last 3 month average sell amount automatically. What is the DAX formula or data base setting in power pivot to make it work?

    example 

    Date     /  Sell amount

    2017-01  58,900

    2017-02  62,900

    2017-03  74,100

    power pivot table

    Last 3 month amount : 65,300

    When I input the new data 

    Date     /  Sell amount

    2017-01  58,900

    2017-02  62,900

    2017-03  74,100

    2017-04  63,400

    the new result will become to 

    power pivot table

    Last 3 month amount : 66,800

    Best regards,

    David

    Tuesday, April 25, 2017 11:11 AM

Answers

  • Excel 2010/2013/2016 Power Query (aka Get & Transform
    Averaging complete and incomplete months is not a valid operation.
    Also months have varying lengths, unless you use a fancy fiscal calendar,
    and might have different active selling days.
    A better way may be to average daily sales in the last nn business days.
    Easier in PQ than in PowerPivot,
    and PQ is available in more versions of Excel.
    http://www.mediafire.com/file/w37ue2tvpf9zdt5/04_27_17.xlsx

    Thursday, April 27, 2017 3:21 PM