cancel
Showing results for
Did you mean:
Highlighted
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.

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))`

1 ACCEPTED SOLUTION

Accepted Solutions
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)

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

5 REPLIES 5
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)

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

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

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
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!!

New Contributor

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?