Show Value of Previous Non Blank if Filter Selected Has No Value

[NeroTolentino]

Hello,

Hope you can help me with a solution.  I need to get the Last Non Blank Value. In the images below, I need Image 2 to show 89.00%, since it has the most recent non blank value. Same if I filter to Dec-2022, I'd like the card to show the most recent non blank which is Jun-2022 score. Image 3 shows the model. The closest solution I found is using LastNonBlank Date but the fields came from the same table so I wasn't able to get the desired solution.

Here the PBI_File for reference. Thanks in advance!

Image 1

Image 2

Image 3

[lbendlin · Accepted solution]

Thank you for providing the sample data. That helps a lot with proposing a potential solution.

There's a problem with your statement

"There is no data for May-2022"

There is in fact data for May - two rows with an empty string. That's not good.  Can you either not have these rows, or replace the empty strings with null ?

Average Score % = 
var m=max(DateTable[Date])
var lm=CALCULATE(LASTNONBLANK(ScoreTable[Date],sum(ScoreTable[Date])),all(ScoreTable),ScoreTable[Date]<=m)
return calculate(AVERAGE(ScoreTable[Score %]),DateTable[Date]=lm)

see attached.

[NeroTolentino]

Exactly how I need it. Thank you!