Window Function with Month Number and Month Name

[berry195]

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

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

This is how Date table looks like

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!

[berry195]

Many thanks for your help!

[AlexisOlson · Accepted solution]

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.