Forum Discussion
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 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
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
6 Replies
v-danhe-msftMicrosoft Employee
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
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
Post Prodigy
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]
)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) )
- 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
