Previous LastNonBlank Value in a measure

[Amaze23]

Hi,

Scenario: In above screenshot we have following data:
Conf Code: Conference code for a conference
Conf Year: Year in which conference happened
Accu Code: Unique to each conference ( Above i have filtered for only one Accu code i.e. "0087")
# of Individuals Retained: Measure which shows the number of Attendees retained
Individuals Retaind Test: Measure showing lastnonblankvalue for a particular conf code.

Individual Retaind test =

CALCULATE (

LASTNONBLANKVALUE (

'Dim - Conferences'[Conf Year],

'Fact - Registrations'[# of Individuals Retained]

),

FILTER (

ALL ( 'Dim - Conferences' ),

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

&& 'Dim - Conferences'[Conf Year] <= MAX ( 'Dim - Conferences'[Conf Year] )

))

Desired Result: 

As "BI20" Conf Code is blank for "2022", "Individual Retained Test" measure should show number from "BI17" Conf Code which it is showing but "PY individual Retaind Test" measure should also show value of "BI16" Conf Code as it is previous to "BI17" until next value fills in.

Kindly help.
#DAXissues #PowerBIdesktop #Needhelp

[Anonymous · Accepted solution]

Hi @Amaze23 ,

You can create a measure as below and check whether that is what you want.

PY Individual Retaind test =
VAR _selyar =
    SELECTEDVALUE ( 'Dim - Conferences'[Conf Year] )
VAR _maxyear =
    CALCULATE (
        MAX ( 'Dim - Conferences'[Conf Year] ),
        FILTER (
            ALLSELECTED ( 'Fact - Registrations' ),
            [# of Individuals Retained] <> BLANK ()
        )
    )
RETURN
    IF (
        ISBLANK ( [# of Individuals Retained] ),
        MAXX (
            FILTER (
                ALLSELECTED ( 'Dim - Conferences' ),
                'Dim - Conferences'[Conf Year] = _maxyear - 1
            ),
            [# of Individuals Retained]
        ),
        MAXX (
            FILTER (
                ALLSELECTED ( 'Dim - Conferences' ),
                'Dim - Conferences'[Conf Year] = _selyar - 1
            ),
            [Individual Retaind test]
        )
    )

If the above one can't help you get the correct result, please share some sample data in table Dim - Conferences and Fact - Registrations(exclude sensitive data) and the calculation logic of [PY Individual Retaind test]. Why the values are all 434? Thank  you.

Best Regards

[ValtteriN]

Hi,

You could create an IF logic to test if the PY value is equal to LASTNONBLANK value and if this is the case filter it out from the data thus returning the value prior to that.