Forum Discussion
Visual Calculation Vs DAX Vs WIN Function
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
I modify the Rows I added Year and Month Number istead Year Month Short and it works.
14 Replies
- Oded-DrorHelper III
Thank you for your replay but still produce same wrong result.
- Oded-DrorHelper III
Still same wrong result.
- Oded-DrorHelper III
Year Month Short
- ThxAlotSuper User
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-DrorHelper III
Here is my table relationship, still not working as you can see from the image.
Date to Sales Order Date
- parry2kSuper User
not sure what your last message was, did you check the attached pbix file?