Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
haputhanthree
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 

haputhanthree_0-1669588117464.png


Expected Result
 Screenshot 2022-11-27 at 23.34.00.png
Link to PBIX file 

1 ACCEPTED SOLUTION
haputhanthree
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

View solution in original post

3 REPLIES 3
haputhanthree
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

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

Jihwan_Kim
Super User
Super User

Hi,

Please check the attached pbix file.

 

Jihwan_Kim_0-1669610987381.png

 

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


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.