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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
berry195
Frequent Visitor

Window Function with Month Number and Month Name

Hello,

 

I have 2 different measures

 

1st one: 

SMA 3 Months = AVERAGEX( WINDOW( -2, REL, 0, REL, SUMMARIZE(ALLSELECTED('Date'),'Date'[Month Number Of Year]) ), [Sum Sales Amount] )

 

berry195_0-1718386081709.png

2nd one (the correct one, the result I expected)

SMA 3 Months = AVERAGEX( WINDOW( -2, REL, 0, REL, SUMMARIZE(ALLSELECTED('Date'),'Date'[Month Number Of Year]) ), [Sum Sales Amount] )

 

berry195_1-1718386164122.png

This is how Date table looks like

berry195_2-1718386253369.png

 

Could you help explain why the 1st one does not work, but the second one works? I am aware that soring alphabetical would mess the result, but why the 1st one gives the sales amount for that month, but not an average (of 3 months, depending on sorting order)?

 

Thank you a lot!

 

 

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

You're using ALLSELECTED inside of an iterator. Understanding what happens in that situation is highly complex so I recommend avoiding that if possible (by defining it as a variable outside of the iterator, for example).

 

Ignoring that, I think the core problem here is that the table you define inside WINDOW doesn't have any corresponding outer value to define the current row in your first example. That is, your WINDOW table is trying to sort on [Month Number Of Year] but that column doesn't exist in your table.

 

I've not tested this but it might work better:

SMA 3 Months =
VAR _Months_ =
    SUMMARIZE (
        ALLSELECTED ( 'Date' ),
        'Date'[Month Number Of Year],
        'Date'[English Month Name]
    )
RETURN
    AVERAGEX (
        WINDOW (
            -2, REL,
             0, REL,
            _Months_,
            ORDERBY ( 'Date'[Month Number Of Year], ASC )
        ),
        [Sum Sales Amount]
    )

I've made three main changes.

  1. Moved ALLSELECTED outside of AVERAGEX.
  2. Included [English Month Name] in the table to use in the window function.
  3. Added and explicit ORDERBY argument.

View solution in original post

2 REPLIES 2
berry195
Frequent Visitor

Many thanks for your help!

AlexisOlson
Super User
Super User

You're using ALLSELECTED inside of an iterator. Understanding what happens in that situation is highly complex so I recommend avoiding that if possible (by defining it as a variable outside of the iterator, for example).

 

Ignoring that, I think the core problem here is that the table you define inside WINDOW doesn't have any corresponding outer value to define the current row in your first example. That is, your WINDOW table is trying to sort on [Month Number Of Year] but that column doesn't exist in your table.

 

I've not tested this but it might work better:

SMA 3 Months =
VAR _Months_ =
    SUMMARIZE (
        ALLSELECTED ( 'Date' ),
        'Date'[Month Number Of Year],
        'Date'[English Month Name]
    )
RETURN
    AVERAGEX (
        WINDOW (
            -2, REL,
             0, REL,
            _Months_,
            ORDERBY ( 'Date'[Month Number Of Year], ASC )
        ),
        [Sum Sales Amount]
    )

I've made three main changes.

  1. Moved ALLSELECTED outside of AVERAGEX.
  2. Included [English Month Name] in the table to use in the window function.
  3. Added and explicit ORDERBY argument.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors