Forum Discussion

Oded-Dror's avatar
Oded-Dror
Helper III
9 months ago
Solved

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-Dror you should use year month column and year-month sort column in the summarization, and the same for order by.

  • 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 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. 

  • 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's avatar
      Oded-Dror
      Helper III

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

      Date to Sales Order Date

  • 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. 

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