cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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

1 ACCEPTED SOLUTION
Frequent Visitor

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 3
Frequent Visitor

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

Frequent Visitor

@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?

Super User

Hi,

Please check the attached pbix file.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors