Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Couple things going on here:
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:
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.
Couple things going on here:
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:
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!!
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |