10 Day AVG with last non-zero average carry forward

[Anonymous]

Hi, 

I would like to create a column or measure that calculates the 10 day AVG. If the oil production for that day is enmpty or 0 however, I would like the program to recall the last non-zero oil production value (according to production date). Thus, if there is no data point, the program is picking the last credible average (as opposed to diluting the average with recent 0's).

I've been able to calculate the average with a "calculate" function however I can't figure out how to carry forward a recent average. If we could calculate the average in a column as opposed to a measure I think it would be easier to recall the "last value". This being said, it's been tricky for me to calculate the correct avg in a column because I'm using the divide function with a static "10" for the denominator. In the cases that there is no oil production, the denominator should only reflect a count of non-zero oil production values within a 10 day range (not a static "10"). 

Perhaps we could referance another count measure? 

I've included a graph below of what I would like the solution to look like (the column on the right is what I'm trying to replicate in power bi). I'm using a direct query data set that updates daily - 

Date	Oil Production	10 Day Oil Production AVG (if oil production > 0)	10 Day Oil Production AVG (trailing with last avg if oil production = 0)
1/22/2019	100	92.9	92.9
1/21/2019	90	91.7	91.7
1/20/2019	110	92.0	92.0
1/19/2019			80
1/18/2019	100	80.0	80
1/17/2019			85
1/16/2019			85
1/15/2019	85	85.0	85
1/14/2019	100	85.6	85.6
1/13/2019	65	83.8	83.8
1/12/2019			90.5
1/11/2019			90.5
1/10/2019	60	90.5	90.5
1/9/2019	70	93.5	93.5
1/8/2019	110	95.0	95
1/7/2019	100	95.0	95
1/6/2019	90	95.0	95
1/5/2019	90	95.0	95
1/4/2019	85	94.5	94.5
1/3/2019	110	96.0	96
1/2/2019	100	94.0	94
1/1/2019	90	93.0	93

[v-danhe-msft]

Hi @Anonymous ,

Based on my test, you could refre to below formula:

c = var previousdate=CALCULATE(MAX([Date]),FILTER(ALL(Table2),Table2[Date]<EARLIER([Date])&&[10 Day Oil Production AVG (if oil production > 0)]<>BLANK()))
return
IF([10 Day Oil Production AVG (if oil production > 0)]=BLANK(),CALCULATE(MAX([10 Day Oil Production AVG (if oil production > 0)]),FILTER(ALL(Table2),Table2[Date]=previousdate)),[10 Day Oil Production AVG (if oil production > 0)])

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous]

I should also mention that the table includes a list of unique completion names ("COMPLETIONNAME"). There are hundreds of completion names in the table (Query1) all with 1 row each per date. The selection I've sent to you is filtered for one completion name. Perhaps the previousdate function is looking to a previous date from a seperate completion name? 

COMPLETIONNAME	PRODUCTIONDATE	0 Downtime BOE	c
QUINTLE 3-10-3XHM	4/14/2019 0:00	458.7	458.7433333
QUINTLE 3-10-3XHM	4/13/2019 0:00	1220.3	1220.333333
QUINTLE 3-10-3XHM	4/12/2019 0:00	1301.1	1301.083333
QUINTLE 3-10-3XHM	4/11/2019 0:00		2314.386667
QUINTLE 3-10-3XHM	4/10/2019 0:00		2225.775
QUINTLE 3-10-3XHM	4/9/2019 0:00		2282.66
QUINTLE 3-10-3XHM	4/8/2019 0:00	706.0	706.0366667
QUINTLE 3-10-3XHM	4/7/2019 0:00		2273.521667
QUINTLE 3-10-3XHM	4/6/2019 0:00		2268.29
QUINTLE 3-10-3XHM	4/5/2019 0:00		2226.163333
QUINTLE 3-10-3XHM	4/4/2019 0:00	1226.5	1226.545
QUINTLE 3-10-3XHM	4/3/2019 0:00	1230.8	1230.768333
QUINTLE 3-10-3XHM	4/2/2019 0:00	1243.2	1243.24

[v-danhe-msft]

Hi @Anonymous ,

Sorry for that I have ordered your data column at first. You could refer to below formula:

Spoiler

c = var previousdate=CALCULATE(MIN([PRODUCTIONDATE]),FILTER(Table1,Table1[PRODUCTIONDATE]>EARLIER([PRODUCTIONDATE])&&[0 Downtime BOE]<>BLANK()))
return
IF([0 Downtime BOE]=BLANK(),CALCULATE(MAX([0 Downtime BOE]),FILTER(ALL(Table1),Table1[PRODUCTIONDATE]=previousdate)),[0 Downtime BOE])

Result:

Regards,

Daniel He

[Anonymous]

Bumping this up - wasn't sure f you've had a chance to see my latest post. @v-danhe-msft 

[Anonymous]

Daniel, I substituted your new formula but I'm still getting the same numbers as before. It did not change the values. 

Shouldn't we have to link the completion name, production link and 0 downtime boe columns somehow (similiar to an "OVER" or "INTERSECT" function in other languages)? Otherwise the "previousdate" function might be looking at cells with previous values from other "completionname"s. The example table I included previoulsy only had one value in "completionname", but the table in Power BI has hundreds of values (as we have hundreds of wells). I'm sorry I didn't include this earlier. I've copy and pasted a table with 2 completion names below. 

Also, I'm not sure if you saw my second question, but I am not sure how to calculate a 10 day AVG for the "0 Downtime BOE" values. I know how to calculate it in a measure, but not a column. I would like the average to exclude cells with no "0 Downtime BOE" value thus decreasing the average denominator below 10.

Ex: if there's only 4 non-zero values within 10 days = (x+x+x+x)/4 rather than (x+x+x+x)/10. 

COMPLETIONNAME	PRODUCTIONDATE	0 Downtime BOE	c
QUINTLE 3-10-3XHM	4/14/2019 0:00	458.7	458.7433333
QUINTLE 3-10-3XHM	4/13/2019 0:00	1220.3	1220.333333
QUINTLE 3-10-3XHM	4/12/2019 0:00	1301.1	1301.083333
QUINTLE 3-10-3XHM	4/11/2019 0:00		2090.431667
QUINTLE 3-10-3XHM	4/10/2019 0:00		2323.055
QUINTLE 3-10-3XHM	4/9/2019 0:00		2314.386667
QUINTLE 3-10-3XHM	4/8/2019 0:00	706.0	706.0366667
QUINTLE 3-10-3XHM	4/7/2019 0:00		2282.66
QUINTLE 3-10-3XHM	4/6/2019 0:00		2289.235
QUINTLE 3-10-3XHM	4/5/2019 0:00		2273.521667
QUINTLE 3-10-3XHM	4/4/2019 0:00	1226.5	1226.545
QUINTLE 3-10-3XHM	4/3/2019 0:00	1230.8	1230.768333
QUINTLE 3-10-3XHM	4/2/2019 0:00	1243.2	1243.24
SEEFELDT 1707 1H-16X	4/14/2019 0:00	25.1	25.07283333
SEEFELDT 1707 1H-16X	4/13/2019 0:00	155.1	155.0728333
SEEFELDT 1707 1H-16X	4/12/2019 0:00	1.7	1.66
SEEFELDT 1707 1H-16X	4/11/2019 0:00	26.7	26.66
SEEFELDT 1707 1H-16X	4/10/2019 0:00		2323.055
SEEFELDT 1707 1H-16X	4/9/2019 0:00	437.0	436.969
SEEFELDT 1707 1H-16X	4/8/2019 0:00	433.0	432.98
SEEFELDT 1707 1H-16X	4/7/2019 0:00	448.3	448.331
SEEFELDT 1707 1H-16X	4/6/2019 0:00	448.2	448.1865
SEEFELDT 1707 1H-16X	4/5/2019 0:00	396.2	396.212
SEEFELDT 1707 1H-16X	4/4/2019 0:00	513.7	513.7173333
SEEFELDT 1707 1H-16X	4/3/2019 0:00	467.9	467.8576667
SEEFELDT 1707 1H-16X	4/2/2019 0:00	494.4	494.42

Thanks again for your help - 

[Anonymous]

Hi Daniel, thank you for your quick response, I appreciate your help. 

1 - I am not getting the same results as you when using that formula. Here is the formula and the results I am getting (the variable names are different than what I initially sent, I switched them in my example to make sure that my goal was understood). The number I sent initially were just examples so they have changed as well:

c = var previousdate=CALCULATE(MAX([PRODUCTIONDATE]),FILTER(ALL(Query1),Query1[PRODUCTIONDATE]<EARLIER([PRODUCTIONDATE])&&[0 Downtime BOE]<>BLANK()))
return
IF([0 Downtime BOE]=BLANK(),CALCULATE(MAX([0 Downtime BOE]),FILTER(ALL(Query1),Query1[PRODUCTIONDATE]=previousdate)),[0 Downtime BOE])

PRODUCTIONDATE	0 Downtime BOE	c
4/14/2019 0:00	458.7	458.7433333
4/13/2019 0:00	1220.3	1220.333333
4/12/2019 0:00	1301.1	1301.083333
4/11/2019 0:00		2314.386667
4/10/2019 0:00		2225.775
4/9/2019 0:00		2282.66
4/8/2019 0:00	706.0	706.0366667
4/7/2019 0:00		2273.521667
4/6/2019 0:00		2268.29
4/5/2019 0:00		2226.163333
4/4/2019 0:00	1226.5	1226.545
4/3/2019 0:00	1230.8	1230.768333
4/2/2019 0:00	1243.2	1243.24

2 - I should have clarified in my initial message, I'm also not sure how to calculate the previous 10 day average in a column (excluding 0's or blanks). I have done it in a measure but I would like to have it in a column if possible. 

Thanks again for your help!