Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AGo
Post Patron
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.

 

Thank you in advance!

1 ACCEPTED SOLUTION
JustJan
Responsive Resident
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 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.
 
2020-07-21 23_25_54-Untitled - Power BI Desktop.png

 

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

View solution in original post

2 REPLIES 2
JustJan
Responsive Resident
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 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.
 
2020-07-21 23_25_54-Untitled - Power BI Desktop.png

 

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

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors