Moving Averages - DAX and Power BI

[mzs1988]

Hi , 

I'm trying to implement a moving average in Dax.  I saw multiple solution and they are working fine.  However I do not understand why the below solution does not work. Espacially that it seems pretty intiuitve. 

The idea is simple.  I have a table that contains all my data 'Query1'  and a calander table 'Calendar Relative' and the relationship is propery defined. Where the Date in Calender is dt in Query1.  

In my measure I use Summarize to return all the items ( ATM devices ) that I would like to calculate the running average for and I return all the dates they where operational in.  Next I use AddColumns and calculate a 3 days running average for each Device and Date it was operational In.  I use DatesInPeriod to do this. 

Below is my code : 

define 
var moving_average = 
ADDCOLUMNS(  SUMMARIZE(Query1   ,'Query1'[device_id] , Query1[dt] ),
"movingAverage" , calculate(average(Query1[completion_time] ) , DATESINPERIOD('Calendar Relative'[Date],Query1[dt] , -3 , DAY) ) , 
"min date" , calculate(min(Query1[dt] ) , DATESINPERIOD('Calendar Relative'[Date],Query1[dt] , -3 , DAY) ),
"max date " , calculate(max(Query1[dt] ) , DATESINPERIOD('Calendar Relative'[Date],Query1[dt] , -3 , DAY) ),
"count trans " , calculate(count(Query1[completion_time]) , DATESINPERIOD('Calendar Relative'[Date],Query1[dt] , -3 , DAY) ))  
    
//evaluate Average_pre_period
//evaluate Average_in_period
//evaluate row("diff" , dif ) 
EVALUATE moving_average

According to my understanding this should calculate the moving average. But what it is currently doing is that is calculates a single day average.  In other word the DatesInPeriod is always returning a single day instead of current date -3 period. 

Can anyone help to explain why this is happening ? 

thanks 

[mzs1988]

thank you @amitchandak for your fast response. 

The measure you gave me shows a single day average. I tried putting in a summarize funcation or a table visuals with all possible machines and dates and gives the same result. This is similar to what I'm seeing in the original code I shared in the post.  

[amitchandak · Accepted solution]

@mzs1988 , Try a meausre like

calculate(averageX(Values('Query1'[device_id]) , calculate(Sum(Query1[completion_time] ))) , DATESINPERIOD('Calendar Relative'[Date],max('Calendar Relative'[Date]), -3 , DAY))

Make sure Calendar Relative is marked as date table

[mzs1988]

Hello Again. 

I turns out that your Measure does work.  The only thing that I had to do was to replace the Date from the data calender with the the actual date in the table , so the measure becomes as follows : 

moving = 
 var maxdate = max(Query1[dt])
 return calculate(averageX(Query1,[completion_time] ) , DATESINPERIOD(Query1[dt],maxdate, -7 , DAY))

Which is strange may I say, I did not expect that to work. 

On a side note , Once I do this change the summarize function that i posted orignally also works