average null values

[faridelmjabber]

Hello,
can someone help me on how can I get the result highlighted in red?

GRAZIE

[Anonymous]

@faridelmjabber - Could you please describe the business logic used to come up with the red numbers?

[faridelmjabber]

@Anonymous 

I would like to fill in empty cells with an incremental average value, example:
if I have the first value 10 and after 3 empty cells you can have another value than 16, the furmula should be:
16-10= 6     6/4= 1.5

so we will have:

10
10 + 1.5 = 11.5 first empty cell
10 + 1.5 + 1.5 = 13 second empty cell
10 + 1.5 + 1.5 + 1.5 = 14.5 third empty cell
16

ask me for poor English as I use google translator

[Anonymous · Accepted solution]

@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]
)

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[faridelmjabber]

@Anonymous 

thanks for the reply, I can't apply your suggestion,
I have just a table, where am I doing wrong?

suppose I would like to get the calculate the weighted average only for lines A (in my case it is the column (codice contatore))

file.pbix 

thanks

[Anonymous]

@faridelmjabber  - Is it possible to add a date table? This solution depends on it. 

[faridelmjabber]

@Anonymous 

I tried to create a new date table, it gives me the same problem, you can give me some suggestions please

thanks