average null values

faridelmjabber - 

Please see attached pbix with both a Power Query and DAX (Measure) solution. The logic for both:

1. Use the value for the day if there is one.
2. Otherwise, find out the previous and next values and calculate the weighted average.

Power Query script:

Note: This script is run for each Tip through a Function which passes the Tip as a parameter.

let
    Source = Table.SelectRows(#"Missing", each [Tips] = Tip),
    #"Sorted Rows" = Table.Sort(Source,{{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    ListValues = #"Added Index"[Original Value],
    Accumulator = List.Skip(List.Accumulate(
        ListValues, 
        {0}, 
        (Accumulated, Current) => 
            Accumulated & 
            {List.Last(Accumulated) + (if Current = null then 0 else 1)}
        )
    ),
    AddIndex = Table.AddColumn(#"Added Index", "Grouping", each Accumulator{[Index]}),
    #"Removed Columns" = Table.RemoveColumns(AddIndex,{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Grouping"}, {{"MinDate", each List.Min([Date]), type date}, {"Value", each List.Max([Original Value]), type number}}),
    Merged = Table.NestedJoin(#"Removed Columns", {"Grouping"}, #"Grouped Rows", {"Grouping"}, "Table", JoinKind.Inner),
    #"Expanded Table" = Table.ExpandTableColumn(Merged, "Table", {"MinDate", "Value"}, {"PrevDate", "PrevValue"}),
    AddNextGrouping = Table.AddColumn(#"Expanded Table", "GroupingNext", each [Grouping] + 1),
    Merged2 = Table.NestedJoin(AddNextGrouping, {"GroupingNext"}, #"Grouped Rows", {"Grouping"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Merged2, "Table", {"MinDate", "Value"}, {"NextDate", "NextValue"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Table1",{"Grouping", "GroupingNext"}),
    DaysBetweenValues = Table.AddColumn(#"Removed Columns1", "Days Between Values", each Duration.Days([NextDate]-[PrevDate])),
    #"Added Custom" = Table.AddColumn(DaysBetweenValues, "Days Since Prev Value", each Duration.Days([Date]-[PrevDate])),
    #"Added Custom 2"= Table.AddColumn(#"Added Custom", "Days Until Next Value", each Duration.Days([NextDate]-[Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom 2", "Value", each if [Original Value] = null then (([Days Between Values]-[Days Since Prev Value]) * [PrevValue] + ([Days Between Values]- [Days Until Next Value]) * [NextValue]) / [Days Between Values] else [Original Value]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"Original Value", "PrevDate", "PrevValue", "NextDate", "NextValue", "Days Between Values", "Days Since Prev Value", "Days Until Next Value"})
in
    #"Removed Columns2"

DAX Measure:

Calculated Value - DAX = 
IF(
    ISBLANK([Original Value - Measure]),
    var _date = SELECTEDVALUE('Date'[Date])
    var _prev_date = LASTNONBLANK(
        FILTER(
            ALL('Date'[Date]),
            'Date'[Date] <= _date
        ),
        [Original Value - Measure]
    )
    var _next_date = FIRSTNONBLANK(
        FILTER(
            ALL('Date'[Date]),
            'Date'[Date] >= _date
        ),
        [Original Value - Measure]
    )
    var _prev_value = CALCULATE([Original Value - Measure], 'Date'[Date] = _prev_date)
    var _next_value = CALCULATE([Original Value - Measure], 'Date'[Date] = _next_date)
    var _days_between_values = DATEDIFF(_prev_date, _next_date, DAY)
    var _days_since_value = DATEDIFF(_prev_date, _date, DAY)
    var _days_until_value = _days_between_values - _days_since_value
    return 
        DIVIDE(
            (_days_between_values-_days_since_value) * _prev_value
            +
            (_days_between_values-_days_until_value) * _next_value,
            _days_between_values
        ),
    [Original Value - Measure]
)