Calculate the most recent value with DAX in Power BI

Hi,

I have survey data many people taking the survey multiple times.

Example below

Each row is a survey response from the date it was taken.

The only way I know which is the pre survey and which is the post survey is by the date taken, so what I’m looking for is a column that tells me if the survey was completed before of after an assigned date.

I’m looking for the answers to be in a calculated column here not a measure.

What do you think?

Ok let’s have a look into this…

While my preference is for measures most of the time, this is an interesting setup.

Try the below and see how you go with this

Max Date = 
VAR CurrentName = 'Survey table'[Name]

RETURN
MAXX(
   FILTER( ALL( 'Survey table' ), 'Survey table'[Name] = CurrentName ),
        'Survey table'[Date Taken] )

From here you can just run a simple IF statement if you need another column to work out which was the most recent survey taken.

Also a key point here…I would rather this be completed in a measure.

It’s a much better way to run almost any calculation here.

Only a small change required for this one

Max Survey Date = 
VAR CurrentName = SELECTEDVALUE( 'Survey table'[Name] )

RETURN
MAXX( FILTER( ALL( 'Survey table' ), 'Survey table'[Name] = CurrentName ),
    'Survey table'[Date Taken] )

Nearly always use measures especially with calculations that are occurring over your fact table.

Thanks
Sam