Forum Discussion

austena229022's avatar
austena229022
Frequent Visitor
1 year ago
Solved

Average Line per Month

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?

 

Need Help
Show and Tell
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    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] ) )
        )
    )

     

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    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's avatar
      austena229022
      Frequent Visitor
      1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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