Forum Discussion
Averagex over multiple dates
Hi,
I have an unrelated calendar table and the below measures in the sales table.
InTransit =
CALCULATE(
COUNT(Sales[ID])
,FILTER(
'Sales'
,Sales[DeliveryDate] < MAX('Dim Calendar'[Date])
&& Sales[ArrivalDate] > MAX('Dim Calendar'[Date])
)
)
InTransit - The number of orders that have been delivered by the end of the month but have not been delivered yet.
I want to calculate the average for the last 2 months as (# of last month's in transit + this moth transit)/2
But the below measure is not working as expected. Look forward to your support.
Average Lst 2 Months =
AVERAGEX(
VALUES('Dim Calendar'[Month])
,[InTransit]
)
Current result
Expected Result
Link to PBIX file
The below measue worked for me.
Average Lst 2 Months =
VAR NumOfMonths = 2
VAR LastCurrentDate =
MAX ( 'Dim Calendar'[Date] )
VAR Period =
DATESINPERIOD ( 'Dim Calendar'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR result =
AVERAGEX (
SUMMARIZE (
CALCULATETABLE ( 'Dim Calendar', Period ),
'Dim Calendar'[Month],
"InTransit", [InTransit]
),
[InTransit]
)
RETURN
result
3 Replies
Hi,
Please check the attached pbix file.
The below measue worked for me.
Average Lst 2 Months =
VAR NumOfMonths = 2
VAR LastCurrentDate =
MAX ( 'Dim Calendar'[Date] )
VAR Period =
DATESINPERIOD ( 'Dim Calendar'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR result =
AVERAGEX (
SUMMARIZE (
CALCULATETABLE ( 'Dim Calendar', Period ),
'Dim Calendar'[Month],
"InTransit", [InTransit]
),
[InTransit]
)
RETURN
resultJihwan_Kim Thank you!
If I want to calculate average of last 12 moths must define 12 variables. Is there any optimization that you could think off to handle that scenario?
