cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fajemile Frequent Visitor
Frequent Visitor

Rolling Average Zero Days

Hi There

 

I am trying to create a moving average. that takes in the account the days that have zero values. The zero days are not in the data itself. So for example I would consider the 11th January 2019 a zero day as the date is missing.

 

1.PNG

This is the dax formula that i used to achieve the moving average.

7DayRollingAverage Decelerations = CALCULATE(SUM(GPS[Decelerations]), DATESINPERIOD(GPS[Session Date],LASTDATE(GPS[Session Date]),7,DAY))/CALCULATE(DISTINCTCOUNT(GPS[Session Date]), DATESINPERIOD(GPS[Session Date],LASTDATE(GPS[Session Date]),7,DAY))

I have had a look at other posts but it seems like everybody has calculated moving averages differently. 

 

I added an IF(ISBLANK function but sadly it led to an error in the visual i was using being displayed

 

7DayRollingAverage Decelerations= if(ISBLANK(SUM(GPS[Decelerations])),0, DATESINPERIOD(GPS[Session Date],LASTDATE(GPS[Session Date]),7,DAY))/CALCULATE(DISTINCTCOUNT(GPS[Session Date]), DATESINPERIOD(GPS[Session Date],LASTDATE(GPS[Session Date]),7,DAY))

Thanks in advance 

1 ACCEPTED SOLUTION

Accepted Solutions
Nick_M New Contributor
New Contributor

Re: Rolling Average Zero Days

Couple things going on here:

  1. Need a dedicated date table to make use of the built-in time intelligence functions
    1. Built a quick one here, but you can see in PQ how I did that. It's using the dates from the fact table, so no need to update
  2. Load the FactTable and the new Date Table 
    1. Need to set the Date table as a Date Table (Data View-->DimDate Table-->Model--> Calendar-->Mark As Date Table

Now we all that in place we can write our function:

7 Day Avg = 
/*Variables are defined once and "Stored", so have the first date of the fact table
This ensures that we do not show an average before we have seven days
This can also be used to make sure we dont into the future if our date table has more dates then the fact */

Var __StartDate= FIRSTDATE( ALL ( FactTable[Session Date]) )
Return

if ( 
    /* Here we are setting the initial boundry of when to start the average */
    CALCULATE(
        LastDate( DimDate[Date]), DATEADD( DimDate[Date], -7, Day)) >= __StartDate,
   /* Here is where we are going to sub in 0 for blanks and then average that */     
CALCULATE( //Calculate here since we are changing the context 
    AVERAGEX( //avergex since we need to interate the data table
        DimDate,
        /* if Total Decl is blank, then we want a zero, else want the actual Total Decl*/
        if( 
            ISBLANK([Total Decl]),0, [Total Decl]  //total decl is just SUM ( FactTable[Decl])
        )
    ),//close off AVERAGEX and then add the filter for calculate
    DATESINPERIOD( DimDate[Date], LASTDATE( DimDate[Date]), -7,DAY ) //Time Intelligence Function, which is why we need a Date Table
    )
)

Ugh, that's hard to read.  Here's a screenshot (and is in the file in the link below)

DAX code.png

 

Then to build the visual, use the Date column from DimDate, add the two measures. But then besure to filter the visual on Total Decl is not blank, else you will get blanks since the 7 day average function is using those dates

Filter.pngFinal Table.png

 

File:

https://1drv.ms/u/s!AoQIGRpzoxRHgbUBMi4XvmxUzy-AwA

5 REPLIES 5
Nick_M New Contributor
New Contributor

Re: Rolling Average Zero Days

Couple things going on here:

  1. Need a dedicated date table to make use of the built-in time intelligence functions
    1. Built a quick one here, but you can see in PQ how I did that. It's using the dates from the fact table, so no need to update
  2. Load the FactTable and the new Date Table 
    1. Need to set the Date table as a Date Table (Data View-->DimDate Table-->Model--> Calendar-->Mark As Date Table

Now we all that in place we can write our function:

7 Day Avg = 
/*Variables are defined once and "Stored", so have the first date of the fact table
This ensures that we do not show an average before we have seven days
This can also be used to make sure we dont into the future if our date table has more dates then the fact */

Var __StartDate= FIRSTDATE( ALL ( FactTable[Session Date]) )
Return

if ( 
    /* Here we are setting the initial boundry of when to start the average */
    CALCULATE(
        LastDate( DimDate[Date]), DATEADD( DimDate[Date], -7, Day)) >= __StartDate,
   /* Here is where we are going to sub in 0 for blanks and then average that */     
CALCULATE( //Calculate here since we are changing the context 
    AVERAGEX( //avergex since we need to interate the data table
        DimDate,
        /* if Total Decl is blank, then we want a zero, else want the actual Total Decl*/
        if( 
            ISBLANK([Total Decl]),0, [Total Decl]  //total decl is just SUM ( FactTable[Decl])
        )
    ),//close off AVERAGEX and then add the filter for calculate
    DATESINPERIOD( DimDate[Date], LASTDATE( DimDate[Date]), -7,DAY ) //Time Intelligence Function, which is why we need a Date Table
    )
)

Ugh, that's hard to read.  Here's a screenshot (and is in the file in the link below)

DAX code.png

 

Then to build the visual, use the Date column from DimDate, add the two measures. But then besure to filter the visual on Total Decl is not blank, else you will get blanks since the 7 day average function is using those dates

Filter.pngFinal Table.png

 

File:

https://1drv.ms/u/s!AoQIGRpzoxRHgbUBMi4XvmxUzy-AwA

fajemile Frequent Visitor
Frequent Visitor

Re: Rolling Average Zero Days

Thank you for the reply @Nick_M I really appreciate it. Sadly I have fallen at the first hurdle as I am not very power bi savvy. 

 

What do you mean by dedicated date table? I had a look at your power bi file and you put those dates in manually but I tried to make a date table in my own file and it was essentially just a copy of one of my other tables; when I tried to build the table I didn't know where to refer it to so I just entered DateTable = GPS (one of my other tables as pictured below) but i could not do GPS[session date} as it only allowed me to do GPS

2.png

 

So I am assuming GPS is my fact table?

 

This was my datetable equation 

3.jpg

 

Do i delete all the columns in this table until i get to session date and then rename it DimDate Table?

 

Also, the 7 day avg is amazing and again thank you so much for the solution! I'm sure this is down to my silly maths but I was wondering why the moving average values before the 18/01 are different to the moving averages calculated in excel. 

 

DateTotal Decl7 Day AvgExcel Calculation
05/01/2019 00:0020  
07/01/2019 00:003382  
08/01/2019 00:002551  
10/01/2019 00:001099  
12/01/2019 00:0022771329.857 
13/01/2019 00:00 1329.857 
14/01/2019 00:00256412131699
15/01/2019 00:00 848.57141696
16/01/2019 00:0013621043.1431408
17/01/2019 00:00 886.14291043
18/01/2019 00:0026821269.2861269
19/01/2019 00:00 944944
20/01/2019 00:0027781340.8571341
21/01/2019 00:00 974.5714975
22/01/2019 00:0028891387.2861387
fajemile Frequent Visitor
Frequent Visitor

Re: Rolling Average Zero Days

Hi @Nick_M I had a further look at creating a DimTable and figured out what you meant. Thank you very much this solution worked perfectly for me and the code you put up was very easy to implement. 

 

Thank you again!!

Nick_M New Contributor
New Contributor

Re: Rolling Average Zero Days

@fajemile glad it worked out!

Highlighted
deanblittle87 New Member
New Member

Re: Rolling Average Zero Days

Hi guys,

Thanks for the work through above. This really helped me with a similar dashboard that I was trying to build (prior 7 day rolling average). 

I have to improvements that I would like to make and I'm desparately in need of help:

 

- Is it possible to have the calculation show the prior 7 day average on the zero days, so it doesn't have to be filtered out? Even on a zero day there will be values to average in the prior 7 days. 

 

- For some reason I can't get the calculation to show on the latest date, it works after the first 6 days and then it stops the day before today (latest date). Is there something that I have to change in the DAX?

Thanks in advance for your assistance.