Issue with rolling 6 month average

[ChrisB1977]

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 numbers is a count of each row by month. The metric is created in my SQL tables and imported into Power BI. This should give me a 6 month average of 50.66*. I've used the following DAX  to calculate my rolling 6 month average:

Orders R 6M Ave = CALCULATE(AVERAGE(table 1[order numbers]),DATESBETWEEN(table 1[date_et],DATEADD(LASTDATE(table 1[date_et]),-6,MONTH),LASTDATE(table 1[date_et])))

This returns a value of 1.00 - any ideas why this is the case? 

Many thanks

Chris

[v-danhe-msft]

Hi @ChrisB1977,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

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

[ChrisB1977]

Hi @v-danhe-msft

That worked, thank you so much! It's also solved several other issues for me so I'm very grateful 🙂

Best regards

Chris

[prakash11440278]

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]

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]

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) )