Average Line per Month

[austena229022]

I am creating a graph of the applications that have been received each month for the past 3 years.

What I ma needing to do is to create a 2 year average line that would show an estimated average from the past 2 years by month - eg: June average for the past 2 years is 1.2k, July average is 3.5k etc to be plotted on the graph

Would anyone know how to do this?

[Anonymous]

Hi @austena229022 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

WINDOW function (DAX) - DAX | Microsoft Learn

Sales measure: = 
SUM( sales[sales] )

WINDOW function (DAX) - DAX | Microsoft Learn

rolling 24 months avg sales measure: = 
VAR _t =
    WINDOW (
        -24,
        REL,
        -1,
        REL,
        ALL ( 'calendar'[Month Year], 'calendar'[Month Year sort] ),
        ORDERBY ( 'calendar'[Month Year sort], ASC )
    )
RETURN
    IF (
        COUNTROWS ( _t ) = 24
            && SUM ( sales[sales] ),
        AVERAGEX (
            WINDOW (
                -24,
                REL,
                -1,
                REL,
                ALL ( 'calendar'[Month Year], 'calendar'[Month Year sort] ),
                ORDERBY ( 'calendar'[Month Year sort], ASC )
            ),
            CALCULATE ( SUM ( sales[sales] ) )
        )
    )

[austena229022]

This is great thank you, it worked when using the COUNTROWS function instead of SUM