Average from value above and below

[Jasel]

Howdy all!

I have a set of data that is not fully complete.  Here is an example:

Date	TMAX	TMIN
19380530	73	60
19380531	80	53
19380601	81	-9999
19380602	75	63

The -9999 shows the incomple data.

Is there a way to check for values above or below in the calumn to figure out an avereage if the above and below values are not -9999?  This is my main ask.

Secondary ask is the only other thing I figure I could do is replace the -9999 with blank or null, but not sure how to do that either since the replace function requires a number value to replace with.  I'm sure I can create anohter column where if the value is -9999 than blank in DAX but that seems messy to me since I have quite a few columns with this issue.

[Phil_Seamark · Accepted solution]

Hi @Jasel,

This would be easier if you have an index column to help determin the rows before/after.

The Query Editor can possibly help with this, allthough you'll have to check your sorting.

Then if you create a calculated table like this you can build simple AVERAGE measures over it

Table = SELECTCOLUMNS(
    FILTER(
        CROSSJOIN ('Table1',SELECTCOLUMNS('Table1',"ID2",[Index],"TMAX2",[TMAX],"TMIN2",[TMIN])),
        [Index]>=[ID2]-1 
        && [Index] <= [ID2]+1 
        && [TMIN2] <> -9999
        ),"Date" , [Date] , "TMIN" , [TMIN2] , "TMAX" , [TMAX2])