dax window function

2 Topics

Remove Filters within Window functions
Hi, I am calculating moving average of 3 weeks on a YTD weekly sales data. Sample Example - Assuming hypothetical data below arranged by weeks from latest weeks as 0 to prior weeks decremented by -1. In my Visual i want to display latest 7 weeks sales and their moving average. Any idea how to ignore filters on visual or page while using a window function ---------INPUT DATA--------- Weeks Sales Moving Avg (Rolling 3 weeks) 0 50 60 -1 60 70 -2 70 80 -3 80 90 -4 90 100 -5 100 110 -6 110 120 -7 120 130 -8 130 140 -9 140 150 ------------------------Expected OUTPUT------------------- Weeks Sales Moving Avg (Rolling 3 weeks) 0 50 60 -1 60 70 -2 70 80 -3 80 90 -4 90 100 -5 100 110 -6 110 120 --------------------OUTPUT Achieved -------------- Weeks Sales Moving Avg (Rolling 3 weeks) 0 50 60 -1 60 70 -2 70 80 -3 80 90 -4 90 100 -5 100 110 -6 110 110 Have a filter on visual to include weeks till -6, after using window function as below value for moving average for week -6 is same 110 but ideally we expect 120. Measure = AverageX( Window(-2, REL,0, REL, SUMMARIZE( ALL(DATA), [Weeks]), Order By ([Weeks], ASC) ), SUm(SAles) )
Solved
pandbh1989
2 years ago Place DAX Commands and Tips
520Views
0likes
1Comment
Help with New Window function
Hi before i used below DAX to calculate running totals and it works fine. Board02P&LRunningTotal = if( HASONEFILTER(TabPLGLItem[GLItem]) , CALCULATE( [Board01P&LAllTransactionAmountwithSign] , ALL(TabPLGLItem[GLItem]) , TabPLGLItem[Index] <= VALUES(TabPLGLItem[Index]) ) , blank() ) Now I am trying my hand at the new Window function and wrote following DAX: Board02aP&LRunningTotal = CALCULATE( [Board01P&LAllTransactionAmountwithSign], WINDOW( 0,ABS, 0,REL, SUMMARIZE(ALLSELECTED(TabPLGLItem),TabPLGLItem[GLItem]), ORDERBY(TabPLGLItem[GLItem]) ) ) Unfortunately, it is not giving me the same result. See below screenshot: It is returning the same previous measure, which was calculating amount per row item: Any idea what might went wrong with the window function? Thanks
Solved
Ackbar-Learner
3 years ago Place DAX Commands and Tips
1.7KViews
0likes
5Comments