Chennai Power BI User Group

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Calculating Moving Average of a Calculated Measure

Jump to Best Answer
  • 1.  Calculating Moving Average of a Calculated Measure

    Posted Jan 18, 2020 07:53 AM
    Hello People,

    I have a very simple table with the following fields
    1) Customer code
    2) Customer Name
    3) Order Date
    4) Order No
    5) Product Code
    6) Product name
    7) Billing Date
    8) Billing Value
    9) Invoice No

    I also have a Date table (with Date, Month, Quarter & Year columns) connected to the Order Date

    I have created a calculated measure for the distinct count of Order No's to get 'Total Orders'.

    Now I am looking for a 3 Months moving average for 'Total Orders'

    I have a page filter for Month and the moving average should automatically change based on the month selected (for example if Dec is selected, the moving average should be calculated for Sep, Oct & Nov) and lastly if month filter is not applied it should calculate the moving average of last 3 months (excluding current month).

    I am very new to Power BI...please help



    ------------------------------
    Rajesh TL

    ------------------------------


  • 2.  RE: Calculating Moving Average of a Calculated Measure
    Best Answer

    Bronze Contributor
    Posted Jan 18, 2020 03:53 PM
    Edited by Hussein Satour Feb 02, 2020 10:29 AM
    Hello,

    If you want to calculate the MA without considering years, you can use the following measure:

    MA3 =
    var theM = MAX(Dates[month])
    var theTable = SUMMARIZE(ALL(Dates[month]), Dates[month], "Valuee", SUMX(Dates, [Total Orders]))
    return
    AVERAGEX (FILTER(theTable, Dates[month] < theM && Dates[month] >= theM-3), [Valuee])


    Let us know if this solution helps you.


    ------------------------------
    Hussein Satour
    Data analyst
    Algiers - DZ
    numidiabi.wordpress.com

    ------------------------------



  • 3.  RE: Calculating Moving Average of a Calculated Measure

    Posted Jan 21, 2020 12:17 AM
    Thank you Hussein for the solution, Appreciate it.


    ------------------------------
    Rajesh TL
    Manager - Analytics
    ------------------------------