Rolling Average Zero Days

[fajemile]

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.

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 

[Anonymous]

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.

[Anonymous · Accepted solution]

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)

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

File:

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

[fajemile]

Hi @Anonymous 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!!

[Anonymous]

@fajemile glad it worked out!

[fajemile]

Thank you for the reply @Anonymous 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

So I am assuming GPS is my fact table?

This was my datetable equation 

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. 

Date	Total Decl	7 Day Avg	Excel Calculation
05/01/2019 00:00	20
07/01/2019 00:00	3382
08/01/2019 00:00	2551
10/01/2019 00:00	1099
12/01/2019 00:00	2277	1329.857
13/01/2019 00:00		1329.857
14/01/2019 00:00	2564	1213	1699
15/01/2019 00:00		848.5714	1696
16/01/2019 00:00	1362	1043.143	1408
17/01/2019 00:00		886.1429	1043
18/01/2019 00:00	2682	1269.286	1269
19/01/2019 00:00		944	944
20/01/2019 00:00	2778	1340.857	1341
21/01/2019 00:00		974.5714	975
22/01/2019 00:00	2889	1387.286	1387