cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Post Patron

## Replay last non blank value

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.

1 ACCEPTED SOLUTION
Responsive Resident

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 periodvar _Filtered = Filter(    ALL('Table'),    'Table'[Period]<= _CurrentPeriod)// calculate averages per period and filter out all 0 valuesvar 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

2 REPLIES 2
Responsive Resident

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 periodvar _Filtered = Filter(    ALL('Table'),    'Table'[Period]<= _CurrentPeriod)// calculate averages per period and filter out all 0 valuesvar 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

Post Patron

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!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors