Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Latest date for group with dynamic date filter

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 - creation01/01/2018
Stage 2 - evaluation01/05/2018
Stage 3 - acceptance01/01/2019
Stage 4 - execution01/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

 

Need Help
  • Tak94's avatar
    Tak94
    6 years ago

    Hi Anonymous ,

     

    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:

     

     

     

     

1 Reply

  • Tak94's avatar
    Tak94
    Frequent Visitor
    6 years ago

    Hi Anonymous ,

     

    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: