topic DAX last non-blank date in DAX Commands and Tips

DAX last non-blank date

mbug123 — Mon, 09 Oct 2023 08:50:11 GMT

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%")

Re: DAX last non-blank date

nirali_arora — Mon, 09 Oct 2023 10:08:33 GMT

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%")

)

Re: DAX last non-blank date

mbug123 — Mon, 09 Oct 2023 10:36:48 GMT

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