Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
RamGSK
Frequent Visitor

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

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,

1 ACCEPTED SOLUTION
mickey64
Super User
Super User

Step 0: I use these data.

mickey64_0-1721577640032.png

 

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

- Before -

mickey64_1-1721577717472.png

- After -

mickey64_2-1721577785075.png

 

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
                              )

mickey64_3-1721578070015.png

 

View solution in original post

2 REPLIES 2
RamGSK
Frequent Visitor

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

mickey64
Super User
Super User

Step 0: I use these data.

mickey64_0-1721577640032.png

 

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

- Before -

mickey64_1-1721577717472.png

- After -

mickey64_2-1721577785075.png

 

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
                              )

mickey64_3-1721578070015.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.