Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
That worked, thank you so much! It's also solved several other issues for me so I'm very grateful 🙂
Best regards
Chris
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
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) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |