Forum Discussion

Nivit's avatar
Nivit
New Member
1 year ago

Rolling Average Graph from each period

Moving Average Price Calculation

 

I'm working on a project where I need to create a line graph displaying the moving average of a Material Price column over a specific period. The period is defined by three columns in my data:

  1. Start Mth: This indicates the starting month from which I will calculate backwards from the most recent month with available price data.
  2. Mth Count: This specifies the number of months to be used for the moving average calculation. (Example : 3 month or 6 month)
  3. Gap Data: This determines how many months should be included from the Start Mth for the calculation. (Example : 0-6 month)
  4. Material: Material in database should be filter before calculation.

I need to create a measure or column that will calculate the average price over this defined period, ensuring that the period is consistent across the graph, based on these three conditions.

Could anyone share their suggestions or provide DAX formulas for calculating this moving average in Power BI?

Thank you in advance for your help!

 

Example Details Table

 

Required Graph

Blue line = Price in table

Yellow dashed Line = Movement of Average from those period price

 

2 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    1 year ago

    Hi Nivit ,

     

    To calculate the moving average of the Material Price column based on your conditions (Start Mth, Mth Count, Gap Data, and Material), you can use DAX to create a dynamic measure. First, ensure you have a Calendar table in your Power BI model. This table should cover a continuous date range that includes all relevant data. If you don't already have a Calendar table, you can create one using the CALENDAR function to generate dates, and then add derived columns for months or years as needed.

     

    Once the Calendar table is set up and connected to your Material Price data via the appropriate date column, you can create a measure to calculate the moving average. Here's a possible DAX formula for the measure:

    Moving Avg Price = 
VAR SelectedMaterial = SELECTEDVALUE('YourData'[Material])
VAR StartMonth = SELECTEDVALUE('YourData'[Start Mth])
VAR MthCount = SELECTEDVALUE('YourData'[Mth Count])
VAR Gap = SELECTEDVALUE('YourData'[Gap Data])
VAR StartDate = EOMONTH(STARTOFMONTH(DATEVALUE(StartMonth)), -Gap)
VAR EndDate = EOMONTH(StartDate, -MthCount + 1)
VAR FilteredTable = 
    FILTER(
        'YourData',
        'YourData'[Material] = SelectedMaterial &&
        'YourData'[DateColumn] >= EndDate &&
        'YourData'[DateColumn] <= StartDate
    )
RETURN
    AVERAGEX(FilteredTable, 'YourData'[Material Price])

    In this formula, the SelectedMaterial variable filters the data to include only the chosen material. The StartMonth variable determines the starting point of the calculation, while MthCount specifies the number of months over which the moving average is calculated. The Gap variable adjusts the start date by skipping the specified number of months. The StartDate and EndDate variables define the range of dates to be used for the moving average. Finally, the FilteredTable variable narrows down the data to include only the relevant records, and the AVERAGEX function computes the average price for that range.

     

    To visualize this moving average in a line graph, place the Date field from your Calendar table on the X-axis and use the Moving Avg Price measure for the Y-axis. Add slicers for Material, Start Mth, Mth Count, and Gap Data to allow for dynamic adjustments in the graph based on these parameters. This approach ensures that the moving average is calculated consistently across the graph while remaining responsive to user-defined inputs.

     

    Best regards,

    • Nivit's avatar
      Nivit
      New Member
      1 year ago

      Thank you for your kindly help

      The error occur about STARTOFMONTH must specify a column.
      So I add column 'StartMonthDate' to convert 'Start mth' to Date format column

       

       

      StartMonthDate = 
DATE(
    YEAR(MAX(MaterialDB[Month])),
    SWITCH(
        MaterialDB[Start mth],
        "Jan", 1, "Feb", 2, "Mar", 3, "Apr", 4,
        "May", 5, "Jun", 6, "Jul", 7, "Aug", 8,
        "Sep", 9, "Oct", 10, "Nov", 11, "Dec", 12,
        BLANK() 
    ),
    1
)

       

       

       Then I modify some of code to match my data.

       

       

      Moving Avg Price = 
VAR SelectedMaterial = SELECTEDVALUE('MaterialDB'[Material&Source])
VAR StartMonth = SELECTEDVALUE('MaterialDB'[StartMonthDate])
VAR MthCount = SELECTEDVALUE('MaterialDB'[Mth Count])
VAR Gap = SELECTEDVALUE('MaterialDB'[Gap data])
VAR StartDate = EOMONTH(STARTOFMONTH(MaterialDB[StartMonthDate]), -Gap)
VAR EndDate = EOMONTH(StartDate, -MthCount + 1)
VAR FilteredTable = 
    FILTER(
        'MaterialDB',
        'MaterialDB'[Material&Source] = SelectedMaterial &&
        'MaterialDB'[Month] >= EndDate &&
        'MaterialDB'[Month] <= StartDate
    )
RETURN
    AVERAGEX(MaterialDB, 'MaterialDB'[Price])

       

       

      Also add Slicer to filter like this

       

      But it occur Moving Avg Price to be data same as Price

      It's still show only 1 period, how to add more period.