Visual Calculation Vs DAX Vs WIN Function

[Oded-Dror]

Hi there,

This is contoso database, I created 3 mesure (showing in the screenshot)
Visual Calculation, DAX measure and Dax using Window Function 3 month moving average on Sales 
As you can see the VC match DAX but Windows Function did not produce the correct results
What I'm I doing wrong here please help with Window functions.
Thanks,
Oded Dror

[parry2k]

not sure what your last message was, did you check the attached pbix file?

[parry2k]

@Oded-Dror see attached, if it still doesn't work for you, share your pbix file. 

[parry2k]

@Oded-Dror @I will send you my file later which works based on the solution I provided earlier. You can compare it at your end and figure it out what's going on. 

[ThxAlot]

When Time Intelligence fuctions are executed, an ALL(), by default, is automatically appended by DAX engine to calculate correct dates; whereas there's no such behaviors for WIN functions. Thus, explicitly remove filtering from 'Calendar'[Year Month Short].

I presume you set up correct relationship between calendar and sales table, the measure can be as simple as

AVERAGEX(
    WINDOW( -2, REL, 0, REL, ORDERBY( 'Calendar'[Year Month], ASC ) ),
    CALCULATE( [Sales Amount], ALL( 'Calendar'[Year Month Short] ) )
)

[Oded-Dror]

Here is my table relationship, still not working as you can see from the image. 

Date to Sales Order Date

[parry2k]

@Oded-Dror in the order by you will be using the sort column not the one on the display (Year Month Short).

Year Month Short column is sorted on another column, and that will be in the order by. 

[parry2k]

@Oded-Dror which one is your sort column?

[Oded-Dror]

Year Month Short

[Oded-Dror]

Still same wrong result.

[parry2k]

@Oded-Dror try this:

AVERAGEX (
    WINDOW ( 
        -2, REL, 
        0,  REL,
        ALLSELECTED ( 'Calendar'[Year Month Sort], 'Calendar'[Year Month] ),
        ORDERBY(
             'Calendar'[Year Month Sort], ASC  )
    ),
    [Sales Amount]
)

[Oded-Dror]

Thank you for your replay but still produce same wrong result.

[parry2k]

@Oded-Dror you should use year month column and year-month sort column in the summarization, and the same for order by.

[Oded-Dror]

[Oded-Dror · Accepted solution]

I modify the Rows I added Year and Month Number istead Year Month Short and it works.