How to write a measure to get average value for particular date which has no data?

[Annu_choubey]

Hi Experts,

Suppose I have below date with some value as well as missing value.

Date Value
8/18/2020---45
8/19/2020---36
8/20/2020---25
8/21/2020
8/22/2020
8/23/2020
8/24/2020---96
8/25/2020---22
8/26/2020---89
8/27/2020
8/28/2020---56
8/29/2020---85

Here we have missing data for  date 8/27/2020 (1 day )and from date 08/21 to 08/23 (3 days)

I want to create a measure in such a way that I can get a average data for missing date.

For Example:-(25+96)/2---from date 08/21 to 08/23 and (89+56)/2--for date 8/27

Thanks,

Annu

[CNENFRNL · Accepted solution]

Hi, @Annu_choubey , pls refer to a DAX solution.

Avg = 
VAR __prev_nonblank =
    LASTNONBLANKVALUE (
        FILTER ( ALL ( DS[Date] ), DS[Date] <= MIN ( DS[Date] ) ),
        MAX ( DS[Value] )
    )
VAR __next_nonblank =
    FIRSTNONBLANKVALUE (
        FILTER ( ALL ( DS[Date] ), DS[Date] >= MIN ( DS[Date] ) ),
        MAX ( DS[Value] )
    )
RETURN
    IF (
        ISBLANK ( MIN ( DS[Value] ) ),
         ( __next_nonblank + __prev_nonblank ) / 2,
        MIN ( DS[Value] )
    )

[Annu_choubey]

Thank You😊!!

[FarhanAhmed]

You can try by going into Power Query

- Duplicate your Value column 

- Fill Orignal Value column by Up

- Fill Duplicated Value column by Down

- Add a custom Column (New Value) by ([Value] +[Value - Copy])/2

- This new column will have your desired results.