Forum Discussion
Solved
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] <...
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
Jihwan_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?