cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Need help with rolling averages DAX formula

I need assistance with my rolling average quick measure.  It produces too much data and I do not know how to fix it.  I can produce the graph I want by going from SQL source to Excel and then to Power BI.  But I need to get it to work by going directly from SQL to Power BI.  I am wanting to produce a line graph report that has the date on the X axis and the 7 day running average on the Y axis. In my example I do NOT have a 7 Day Running Average for the first 6 days because I need to have 7 days of data first in order to produce that running average.  In Excel it’s easy to make sure that does not happen. However, in my DAX formula it does not care about not having 7 days of data first to produce a 7 Day Running Average and produces data for the first six days anyhow.  This produces an incorrect line graph.  I want to note that I do have a “Calendar” table created and made a relationship between it and my data table called “Running Average.”  My X axis should show/start with 03/01/17 BUT the first data point should not appear until 03/07/17.  And I definitely do not want “0” to be the value from 03/01 to 03/06 either.  I need them to be blank. Anyone have any suggestions?  What I want my data results to be are below and also my DAX formula.  Note: In actuality, I will be doing a rolling year with my data and just used 7 days in my example for simplicity.

7DayRunningAverage =

CALCULATE(

    AVERAGE('Running Average'[daily_average]),

    DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-6, DAY)

)

 

date

daily_average

7 Day Running Average

3/1/2017

0

 

3/2/2017

0.017766204

 

3/3/2017

0

 

3/4/2017

0.002819865

 

3/5/2017

0

 

3/6/2017

0.032407407

 

3/7/2017

0.032407407

99.98779987

3/8/2017

0

99.98779987

3/9/2017

0.028750117

99.98623074

3/10/2017

0.001177183

99.98606257

3/11/2017

0

99.98646541

3/12/2017

0

99.98646541

3/13/2017

0

99.99109504

3/14/2017

0.003948045

99.99516066

3/15/2017

0.003589744

99.99464784

3/16/2017

0.010624033

99.99723728

3/17/2017

0

99.99740545

3/18/2017

0.008641975

99.99617089

3/19/2017

0.236237374

99.96242269

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

I think you can construct a variable for the first date and use it in a comparison clause e.g.

Var StartDate = FIRSTDATE( ALL ( Table4[date]) )
RETURN
IF (
    CALCULATE( LASTDATE(DateTable[Date]), DATEADD(DateTable[Date], -6, DAY)) >= StartDate,
-- now put your formula here

I don't see where the figures for '7 day average' in your table come from, though

View solution in original post

7 REPLIES 7
Highlighted
Super User II
Super User II

I think you can construct a variable for the first date and use it in a comparison clause e.g.

Var StartDate = FIRSTDATE( ALL ( Table4[date]) )
RETURN
IF (
    CALCULATE( LASTDATE(DateTable[Date]), DATEADD(DateTable[Date], -6, DAY)) >= StartDate,
-- now put your formula here

I don't see where the figures for '7 day average' in your table come from, though

View solution in original post

Highlighted

The figures I show for 7 day rolling average are from the original Excel spreasheet version.  I am moving away from the Excel version and pulling data from SQL directly now so those number need to be calculated through DAX somehow.  The first 6 dates do not have a 7 day rolling average because there is not 7 previous days of data to create an average.  

Highlighted

When I say that I don't know where the figures come from, I don't mean the source.  I mean 'what calculation produces those figures?' because I might be missing something but I can't make e.g. 99.98779987 from the 7 previous values in the other column.

 

"The first 6 dates do not have a 7 day rolling average because there is not 7 previous days of data to create an average." Yes, i understand that part.  The DAX i provided should help you with that.

 

Let me know how you get on with it.

Highlighted

Hello HotChilli,

 

I entered your additional DAX to my DAX and it produces data with no errors. However, it still produces data I should not have or want.  The calculation is to sum up the "daily_average" for the current day plus the previous 6 days and then divide that by 7.  However, if there are not a full 7 days of data then the value for that day should be blank.  This is why, in my sample data, 03/01 through 03/06 do not have a 7 day running average because there is not enough data. For example, 03/01 does not have a 7 day running average because there is not a daily average available for 02/23 through 02/28 and 03/02 does not because there is not a daily average for 02/24 through 03/01.  The DAX is still computing data for those rolling averages on those days, which I believe may be just the daily average value for that day.  Make sense or did I just make this more muddy?  I do appreciate all your help.

Highlighted

Not more muddy but you've re-explained something i already understand.  My question is to do with the 7 day averages e.g 99.98779877 in your provided data.  Here's what mine look likeCapture.PNG

Let me see the calculated column DAX you have now

Highlighted

Hello HotChilli,

 

Sorry for the delay.  My "7 Day Running Average" column is calculated for a specific date by this formula:

(1- Average (most recent date daily_average value plus previous 6 Days))*100.  Therefore, I am always looking to have the "7 Day Running Average" column to have a value, based on the formula, for the most recent date plus the previous 6 days.  And I need that value to be BLANK, not Zero or any other value or ERROR, for ALL days older than 6 days no matter how far back the data goes in order for my line graphs to look correct.  I will have two separate line graphs.  One showing the past 14 days of daily average and the other being the SAME 14 days but only showing the data points for the previous 7 Day Running Average.  I know I am making this more complicated than it should be that is why I am asking for assistance.  Graphs, below:

 

2019-03-22_15h18_37.png2019-03-22_15h18_55.png

Highlighted

Your formula works but now issues with creating a line graph.  Thanks for your help.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors