Latest date for group with dynamic date filter

[cmr2112]

Hello all

Hopefully this is the correct forum, but please correct me if I'm wrong.

I'm trying to filter the contents of a visual to show only the 'latest' record within a group, relative to a date filter. I can identify the latest record by group, but I'm struggling to make this work with a date filter.

Here's the problem in a nutshell... Projects are created and assessments are made against these projects as they progress through a lifecycle. Each project can have 1 to n assessments, each of which has a start date. Each assessment represents a 'stage' (e.g. identification, evaluation, implementation) of the project lifecycle. I want to be able to 'go back in time' and show what the 'latest' project assessment was relative to my selected date.

So, for example.... Project Arcturus is created and progresses through the stages thus:

Stage 1 - creation	01/01/2018
Stage 2 - evaluation	01/05/2018
Stage 3 - acceptance	01/01/2019
Stage 4 - execution	01/05/2019

Showing all dates, I would expect Stage 4 to be the latest. however, if set my date filter to end on the 1st March 2019, I want the 'latest' to be Stage 3 and stage 4 to not be visible (in fact I only want to show the latest in each case, but that's another conversation...)

I've dug around a little and found the DAX for getting the latest by group, but this doesn't extend to being dynamic enough to handle the date filter.

The data model looks like this:

Tables are:

Project: pretty obvious

Stage: the stage the project is at - evaluataion, implementation etc.

Date: again, pretty obvious

Assessment: Actions performed on the project .

The DAX for the IsLatestAssessment measure is:

IsLastestAssessment = if

(

  calculate

  (

    max ( Assessment[Date] ),

    ALLEXCEPT

    (

      Assessment,

      Assessment[ProjectId]

    )

  ) = max(Assessment[Date]) , 1, 0

)

I'll admit my DAX is pretty weak (I could probably do this in SQL with my eyes shut...), hence this post. hopefully this is a simple one can can be answered easily. anyway, I'd appreciate any help.

Thanks in advance

CMR

[Tak94 · Accepted solution]

Hi @cmr2112 ,

You can try something like this:

Latest = 
VAR maxdate = MAX(Assessment[Date])
VAR project = SELECTEDVALUE(Assessment[Projectid])
RETURN CALCULATE(SELECTEDVALUE(Assessment[Stageid]),Assessment[Projectid]=project,
                                    FILTER(Projects,Assessment[Date]=maxdate))

This will return the latest stage for a given date filter over each project_id. I added a new dummy project Beta to illustrate this below: