Average of Previous value and next value for Nulls (Average of row after and row before in Power BI)

[RamGSK]

Hi,

Can someone advise how to calculate average of previous value and next value when we have a blank value in the Sales field in Power BI.

E.g., Scenario:

Date	Sales
1-Jul	10
2-Jul
3-Jul	30
4-Jul
5-Jul	50
6-Jul
7-Jul	70
8-Jul
9-Jul	90
10-Jul

Expected Output:

Date	Sales	Expected Sales
1-Jul	10	10
2-Jul		20
3-Jul	30	30
4-Jul		40
5-Jul	50	50
6-Jul		60
7-Jul	70	70
8-Jul		80
9-Jul	90	90

Thanks,

[RamGSK]

Can this be achieved without Index as it is showing incorrect values when slicer is applied for e.g., on Date, etc.

[mickey64 · Accepted solution]

Step 0: I use these data.

Step 1: I add a 'Index' column on Power Query Editor.

- Before -

- After -

Step 2: I add 3 colmuns to the 'DATA' colum on Power BI Desktop.

    PrevDay Sales = LOOKUPVALUE(DATA[Sales],DATA[Index],'DATA'[Index]-1) 

    NextDay Sales = LOOKUPVALUE(DATA[Sales],DATA[Index],'DATA'[Index]+1)

    Expected Sale = IF('DATA'[Sales]>0,

                                'DATA'[Sales],

                                ('DATA'[PrevDay Sales]+'DATA'[NextDay Sales])/2

                              )