DAX last non-blank date

[mbug123]

Hello,

I have created a report that lets us see survey data over time. I want my matrix in my report to display the following based on filter context:

- Current survey percentage 

- Previous survey percentage

I have created the below measure for Current survey percentage. 

I assume I need to use CALCULATE for the Previous Survey Result measure. I tried to use DATEADD with CALCULATE to get the desired result, but because the surveys are all on arbitary dates this doesn't work. 

Can anyone help

Current survey percentage 

current_percentage =

VAR TotalPercentage =

    SUMX (

        SUMMARIZE (

            'Results_Consolidated',

            'Results_Consolidated'[question_shortened],

            'Results_Consolidated'[question_category],

            'Results_Consolidated'[percentage],

            'Results_Consolidated'[survey_date],

            'Results_Consolidated'[demographic_category],

            'Results_Consolidated'[demographic_subcategory],

            'Response Hierarchy'[response_text],

            'Response Hierarchy'[response_text_groups],

            'Response Hierarchy'[response_text_subgroups]

        ),

        [percentage]

    )

RETURN

    FORMAT(TotalPercentage, "0.0%")

[nirali_arora]

You can try the following measure

Previous Survey Percentage =

VAR CurrentSurveyDate = MAX('Results_Consolidated'[survey_date])

VAR PreviousSurveyDate =

CALCULATE(

MAX('Results_Consolidated'[survey_date]),

FILTER( 'Results_Consolidated', 'Results_Consolidated'[survey_date] < CurrentSurveyDate ) )

RETURN

IF(

ISBLANK(PreviousSurveyDate),

BLANK(),

VAR TotalPercentage =

SUMX (

SUMMARIZE (

'Results_Consolidated',

'Results_Consolidated'[question_shortened],

'Results_Consolidated'[question_category],

'Results_Consolidated'[percentage],

'Results_Consolidated'[survey_date],

'Results_Consolidated'[demographic_category],

'Results_Consolidated'[demographic_subcategory],

'Response Hierarchy'[response_text],

'Response Hierarchy'[response_text_groups],

'Response Hierarchy'[response_text_subgroups] ), [percentage] )

VAR PreviousTotalPercentage =

CALCULATE(

SUMX (

SUMMARIZE (

'Results_Consolidated',

'Results_Consolidated'[question_shortened],

'Results_Consolidated'[question_category],

'Results_Consolidated'[percentage],

'Results_Consolidated'[survey_date],

'Results_Consolidated'[demographic_category],

'Results_Consolidated'[demographic_subcategory],

'Response Hierarchy'[response_text],

'Response Hierarchy'[response_text_groups],

'Response Hierarchy'[response_text_subgroups] ), [percentage] ),

FILTER( ALL('Results_Consolidated'),

'Results_Consolidated'[survey_date] = PreviousSurveyDate ) )

RETURN

IF(

ISBLANK(PreviousTotalPercentage),

BLANK(),

FORMAT(PreviousTotalPercentage, "0.0%")

)

)

[mbug123]

Thank you very much, unfortunatly though it just displays blanks.