Running Average of Group Data

Question

Hello, I am new to Power BI and DAX. I am trying to create a measure for calculating the moving&nbsp;average of 3&nbsp;monthly sales by shops.&nbsp;&nbsp;On the dashboard there is a slicer for shops, and also a timeline slicer for date, so the user can analyze the dashboard data for a month or quarter. I want to add a moving average line in the sales bar chart by months.&nbsp;Please advise how should I set the DAX formula.&nbsp;Below a&nbsp;simplified example table:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;I have also created a Calendar table from 2014-1-1 to 2016-12-31:&nbsp;&nbsp;I searched the internet and found below DAX for moving Sum. However, I don't know how to modify it so that it will be done by Shop as well.&nbsp;[3 Month Moving Sum Sales] =CALCULATE([Sales], &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DATESINPERIOD(Calendar[Date], &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LASTDATE(Calendar[Date]),-3, Month&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;Many thanks in advanced!!!&nbsp;

sean · Answer

desiree&nbsp;This should work... Let me know...&nbsp;3 Month Moving Sum =
CALCULATE (
    [Sales],
    ALLEXCEPT ( 'Table', 'Table'[Store] ),
    DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, MONTH )
)&nbsp;EDIT: You could use the above Measure&nbsp;to also calculate the 3 Month Average below ...&nbsp;3 Month Moving Avg =
DIVIDE (
    [3 Month Moving Sum],
    CALCULATE (
        DISTINCTCOUNT ( 'Calendar'[Date] ),
        DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, MONTH )
    ),
    0
)&nbsp;&nbsp;Hope this helps!&nbsp;Let me know...&nbsp;

v-sihou-msft · Answer

desiree
&nbsp;
Please refer to steps below:

In order to rank values, replace “na” sales with 0 in Query Editor. 
Create a new column with following formula:
Year&amp;Month = 
VAR string =
    YEAR ( 'Table'[Date] ) &amp; MONTH ( 'Table'[Date] )
RETURN
    ( VALUE ( string ) )

Create a measure with&nbsp;following formula:
3 Month Moving Avg = 
VAR FirstD =
    FIRSTDATE (
        DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, MONTH )
    )
VAR LastD =
    LASTDATE (
        DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, MONTH )
    )
RETURN
    (
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            'Table'[Date] &gt;= FirstD,
            'Table'[Date] &lt;= LastD
        )
            / CALCULATE (
                DISTINCTCOUNT ( 'Table'[Year&amp;Month] ),
                'Table'[Date] &gt;= FirstD,
                'Table'[Date] &lt;= LastD
            )
)

Put Hierarchy Slicer, Timeline and “Line and stacked column chart” into the page. Select 'Calendar'[Date] for Timeline. Select Area and Store for Hierarchy Slicer. Select Date(axis), Sales(Column) and 3 Month Moving Avg(Line) for “Line and stacked column chart”

Then the chart will display according to both slicers as below.

&nbsp;
Regards,

desiree · Answer

Sean&nbsp;, DAX formula pop error when I used below.&nbsp;&nbsp;3 Month Moving Sum =
CALCULATE (
    [Sales],
    ALLEXCEPT ( 'Table', 'Table'[Store] ),
    DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, MONTH )
)&nbsp;It said I may need to add Sum,Min, Max etc for [Sales]. So, I modified the formula to below.&nbsp;3 Month Moving Sum =
CALCULATE (
    Sum([Sales]),
    ALLEXCEPT ( 'Table', 'Table'[Store] ),
    DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, MONTH )
)&nbsp;and then, create the &nbsp;[3 month moving avg] as per your suggestions.&nbsp;When I tried to drag this measure to chart, nothing shows. Did I do something wrong?&nbsp;Sorry that I am really new to Power BI and DAX... would you mind sending me an example (.pbix) if you have time?

sean · Answer

What is the name of your 'Table' - did you change 'Table' and 'Calendar' in the formulas to match your own Table names?

Then do the Measures work when you use in a table or matrix?

Can you post a screenshot of what happens when you use the Measures?

desiree · Answer

Sean&nbsp;, Sorry that I just realize my table is as below.. Actually, I need to calcuate the moving average for each&nbsp;Area which is a slicer in my dashbaord...&nbsp;and a area will include many stores. &nbsp; Sorry for confusion. &nbsp; (I am trying to screen capture my dashboard, hope above information helps at this moment)&nbsp;

Forum Discussion

Running Average of Group Data

9 Replies

Related content

Running average but exluding qurterrs with no data

Running Average of Group Data

Grouped Average

Running Maximum

average by group