Modify the DAX for LASTNONBLANKVALUE for a Non Date Context

[Amaze23]

Hello,
There is a DAX query which i am unable to resolve.

Background of the screenshot:  In the above screenshot we have date for conferences that happens almost every year.

Conf Code - Conference Code assigned to a conference for a particular year and changes slightly every year
Accu Code - It is unique to every Conference and remains same.
Conf Year - Year in which conference has happened.
# Individuals Retained - it represents the no. of attendees got retained.
# Individuals Retained PY - it represents the no. of attendees got retained from past year.

Query & Desired Result:  

Suppose a new conference "CET22A" happens this year i.e. "2022" but the "# Individuals Retained" measure is blank then "# Individuals Retained PY" measure should show the last non blank value of "# Individuals Retained" measure which is 24 in above case and repeat it until "# Individuals Retained" has a value as in the screenshot for this particular conference only.

Current Measure in use:

# of Individuals Retained PY =

var py = max('Dim - Conferences'[Conf Year]) -1

var acc = MAX('Dim - Conferences'[Accu Code])

return

if(HASONEVALUE('Dim - Conferences'[Conf Code]),

CALCULATE( [# of Individuals Retained],

filter(all('Dim - Conferences'),

'Dim - Conferences'[Conf Year]= py && 'Dim - Conferences'[Accu Code]=acc) ),

CALCULATE([# of Individuals Retained],

filter(all('Dim - Conferences'),

'Dim - Conferences'[Conf Year]= py )))

I cant share the data due to privacy reasons.

Best Regards

#DAX #Desktop #DAXissues

[amitchandak · Accepted solution]

@Amaze23 , Try a measure like

calculate(lastnonblankvalue(Table[Year],[# Individuals Retained]), filter(all(Table), [Accu Code] = max([Accu Code] ) && [Year] <= Max(Table[Year])))

[Amaze23]

@amitchandak : Thanks, it worked great! you used lastnonblankvalue & tweaked use of conf year as Conf year <= Max (conf year), can you put light on this ?

Actually it looks simple but i am not able to get it.