Forum Discussion

desiree's avatar
desiree
Frequent Visitor
10 years ago

Running Average of Group Data

Hello, I am new to Power BI and DAX. I am trying to create a measure for calculating the moving average of 3 monthly sales by shops.    On the dashboard there is a slicer for shops, and also a time...
Need Help
desiree's avatar
desiree
Frequent Visitor
10 years ago

@Sean , I corrected my Calandar table. Now, the Moving Sum shows the same value for all months. Any ideas?

 

Thank you in advanced :)

v-sihou-msft's avatar
v-sihou-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

desiree

 

Please refer to steps below:

  1. In order to rank values, replace “na” sales with 0 in Query Editor.
  2. Create a new column with following formula:
    Year&Month = 
VAR string =
    YEAR ( 'Table'[Date] ) & MONTH ( 'Table'[Date] )
RETURN
    ( VALUE ( string ) )
  3. Create a measure with 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] >= FirstD,
            'Table'[Date] <= LastD
        )
            / CALCULATE (
                DISTINCTCOUNT ( 'Table'[Year&Month] ),
                'Table'[Date] >= FirstD,
                'Table'[Date] <= LastD
            )
)
  4. 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.

 

Regards,

  • MuratAksakal's avatar
    MuratAksakal
    Regular Visitor
    9 years ago

    I think this solution doesnt meet the request.

     

    We need to running average calculation what the slicer selected on the dashboard. Another description average change when the slicer change. I think There sould be dynamoc filter which select the slicer value.