Forum Discussion

Oded-Dror's avatar
Oded-Dror
Icon for Helper III rankHelper III
10 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 t...
Need Help
  • Oded-Dror's avatar
    Oded-Dror
    10 months ago

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

ThxAlot's avatar
ThxAlot
Icon for Super User rankSuper User
10 months ago

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
    Icon for Helper III rankHelper III
    10 months ago

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

    Date to Sales Order Date