DAX - How to get the latest date in at table?

[jayjay0306]

Hi people,

I hope you ca help me with a small challange:

I have table (visualization) which shows a customer survey (example with 2 customers):

What I need to extract is simply the latest "answer" from each question, from each customer.

I have tried to make the following measure "Lastest survey date":

Latest Survey Date = 
VAR GroupByCustCode= ALLEXCEPT ( Customer, Customer[Customer Code])
RETURN
CALCULATE ( MAX ( 'Surveys'[survey_date_key] ),GroupByCustCode)

But this gives me the result:

What I need is this:

Can someone please show me what I am missing in my measure?

Thanks.

Br,

JayJay0306

[v-chenwuz-msft]

Hi @jayjay0306 

You need create a measure to replace the field Answer with this

Answer_m =
CALCULATE(
    MAX( 'Customer'[Answer] ),
    FILTER( 'Customer', [survey_date_key] = [Latest Survey Date] )
)

Or change your latest survey date to this:

Latest Survey Date =
VAR GroupByCustCode =
    ALLEXCEPT( Customer, Customer[Customer Code] )
VAR _last =
    CALCULATE( MAX( 'Customer'[survey_date_key] ), GroupByCustCode )
VAR _answer =
    CALCULATE(
        MAX( 'Customer'[Answer] ),
        FILTER(
            ALL( 'Customer' ),
            [Customer Code] = MAX( 'Customer'[Customer Code] )
                && [Business Name] = MAX( 'Customer'[Business Name] )
                && [Question] = MAX( 'Customer'[Question] )
                && [survey_date_key] = _last
        )
    )
RETURN
    IF( MAX( 'Customer'[Answer] ) = _answer, _last, BLANK() )

I put my pbix file in the attachment you can reference.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jayjay0306]

Hi Team _ chenwu zhu,

Thanks a lot. This is exactly the result I am looking for. However, I am afraid, I haven't been specific enough. The problem is, that my datamodel consists of several tables. I have tried to edit your script to fit my datamodel:

Latest Survey Date = 
VAR GroupByCustCode =
    ALLEXCEPT( Customer, Customer[Customer Code] )
VAR _last =
    CALCULATE( MAX( 'Executed Survey'[Survey Completion Date] ), GroupByCustCode )
VAR _answer =
    CALCULATE(
        MAX( 'Executed Survey'[Answer] ),
        FILTER(
            ALL( 'Customer' ),
            [Customer Code] = MAX( 'Customer'[Customer Code] )
                && [Business Name] = MAX( 'Customer'[Business Name] )),
        FILTER(
            ALL( 'Question Attributes' ),
            [Question] = MAX( 'Question Attributes'[Question] )),
        
        FILTER(
            ALL('Executed Survey'),
                'Executed Survey'[Survey Completion Date] = _last
        )
    )
RETURN
    IF( MAX( 'Executed Survey'[Answer] ) = _answer, _last, BLANK() )

but unfortunately I get my original result:

Can you see what I do wrong?

thanks.

Br,

Jayjay0306

[v-chenwuz-msft]

Hi @jayjay0306 , maybe your have Incorrect relationship or no relationships beteween these tables.

or  crossjoin these tablas into one table witch table like the table you show .

Can you share you pbix file after removing the sensitive information.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AllisonKennedy]

@jayjay0306  If you don't put the Answer column in your visual you could try using LASTDATE or something similar:

Latest Answer =
CALCULATE ( SELECTEDVALUE ( 'Surveys - Executed Survey Attributes'[Answer] ),LASTDATE(  'Surveys - Executed Survey Attributes'[Survey Date] ) )

[jayjay0306]

Hi AllisonKennedy,

Thanks, but I do need the Answer column in the table. 😞

Br,

Jayjay0306

[AllisonKennedy]

@jayjay0306  Can you share your data model please? We need all the info to be able to help effectively. Which table do each of these values come from?