Forum Discussion

ChrisB1977's avatar
ChrisB1977
Frequent Visitor
7 years ago
Solved

Issue with rolling 6 month average

Hi,   I'm having some trouble with a rolling 6 month average. I have the following order data:     The data is taken from a count of orders - each order has its own row, and the order numb...
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi ChrisB1977,

    Based on my test, you could refer to below steps:

    Create a calender table:

    Table = CALENDARAUTO()

    Create a measure:

    Orders R 6M Ave = DIVIDE(CALCULATE(SUM(Table1[Order number]),DATESINPERIOD('Table'[Date],MAX('Table1'[Month]),-6,MONTH)),6)

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

     

prakash11440278's avatar
prakash11440278
Icon for Post Prodigy rankPost Prodigy
7 years ago

Try to create calcualted measure like below.

 

Orders R 6M Ave =
VAR DateFilter =
     DATESINPERIOD(
          table 1[date_et],
          MAX(table 1[date_et]),
          -6,
           Month
           )
RETURN
          AVERAGEX(
          DateFilter,
          [Sum of Order numbers]
           )

ChrisB1977's avatar
ChrisB1977
Frequent Visitor
7 years ago

Hi prakash11440278,

 

That just returned a total of the order numbers unfortunately, rather than the average - is there something else I could try?

 

Many thanks

 

Chris

  • prakash11440278's avatar
    prakash11440278
    Icon for Post Prodigy rankPost Prodigy
    7 years ago

    Please try the below.

     

    Orders R 6M Ave =
    VAR DateFilter =
    DATESINPERIOD(
    table 1[date_et],
    MAX(table 1[date_et]),
    -6,
    Month
    )
    VAR RollingSUM =
    CALCULATE(
    [Sum of order numbers],
    DateFilter
    )
    RETURN
    DIVIDE( RollingSUM, COUNTROWS( DateFilter) )