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.
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.
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.
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.
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: