Replay last non blank value

[AGo]

Hello,

I've got a line chart with incremental hour in my axis, in my table there could be zero, one or more values for each hour.

The basic function I'm starting from is calculate(average([value]), [item]="ItemINeed"), but I need my graph to substitute blank hours with the last single available value.

For example I've got value 40 and 50 at 9AM, no values at 10AM, value 60 at 11AM: my graph should prompt 45 at 9AM, 50 at 10AM and 60 at 11AM.

Is it possible in a dax measure? I think the problem is getting outside the time context of the line chart axis that acts like a filter on my measure also if I use variables.

Thank you in advance!

[JustJan · Accepted solution]

Hello

I think it is possible with a measure like this:

RT = 
var _CurrentPeriod = SELECTEDVALUE('Table'[Period])

// select all periods upto and including the current period
var _Filtered = 
Filter(
    ALL('Table'),
    'Table'[Period]<= _CurrentPeriod
)

// calculate averages per period and filter out all 0 values
var result = 
TOPN(1,
Filter(
    SUMMARIZE(
            _Filtered,
            'Table'[Period],
         "AVG",[Measure]
         ),
    'Table'[Period]<= _CurrentPeriod && [AVG]<>0
    ),
'Table'[Period],
desc
)

// have to do a sumx here because the result is a table (with one row)
return 
sumx(result,[AVG])

My sample data has period 2 is blank and period 5 as "0" value and as you can see the line continues with the previous value.

So the only thing you have to make sure that you have a period value (your 9, 10 ,11 AM etc.)

Hope this helps,

Jan

if this is a solution for you, don't forget to mark it as such. thanks

[AGo]

Not exactly what I needed but, your tips to use ALL() and TOPN() function in a SUMX() allowed me to solve it.

Thanks a lot!