Forum Discussion

spandy34's avatar
spandy34
Responsive Resident
2 years ago
Solved

View Records with the Maximum Date

I have the report below which lists projects of different Project Life Cycle Phase.

I need to display each the maximum Due date for Project Life Cycle Phase relating to each project

 

Ive tried 

Tasks Max Date =
GROUPBY (
    'Tasks Main',
    'Tasks Main'[Mission ],
   'Tasks Main'[Project ],
    'Tasks Main'[Project Life Cycle Phase],
    "Max Date",
    MAXX ( CURRENTGROUP (), MAX ( 'Tasks Main'[Due Date:  ] ) )
 
but it is returning the max date as 
 

 

talespin danextian tamerj1 amitchandak 

 

 

 

 

 

 

 

 

 

 

So based on the above I would like to see:-

 

Mission Attract and Retain Discovery & Design and the Maximum date for that Group

Mission Attract and Retain Closure and the Maximum date for that Group

(please ignore date format as these will be in date format)

 

Can someone please advise how I could approach this please?

 

Need Help
  • danextian's avatar
    danextian
    2 years ago

    Hi spandy34 ,

     

    try this:

    CALCULATE (
    MAX ( 'tasks main'[due date] ),
    ALLEXCEPT (
        'tasks main',
        'tasks main'[mission],
        'tasks main'[project],
        'tasks main'[life cycle phase]
    )
)
  • talespin's avatar
    talespin
    2 years ago

    hi spandy34 

     

    Replace ORD_ID and MLSTN with the fields you want to get the maximum date for. Similarly you can add more fields.

    In CALCULATE , I am removing all filters and adding only those fielters for which I want to get the maximum date.

    This is a Calculated Column

    ------------------------------------

    Max Date by =
    VAR _Ord_ID = Orders[ORD_ID]
    VAR _MLSTN = Orders[MLSTN_CD]

    RETURN CALCULATE( MAX(Orders[RECEIVED DATE]), REMOVEFILTERS(), Orders[ORD_ID] = _Ord_ID && Orders[MLSTN_CD] = _MLSTN)
     

     

5 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi spandy34 ,

     

    try this:

    CALCULATE (
    MAX ( 'tasks main'[due date] ),
    ALLEXCEPT (
        'tasks main',
        'tasks main'[mission],
        'tasks main'[project],
        'tasks main'[life cycle phase]
    )
)
  • spandy34's avatar
    spandy34
    Responsive Resident
    2 years ago

    That is brilliant ! Thank you for your help. I really appreciate it.

  • talespin's avatar
    talespin
    Solution Sage
    2 years ago

    hi spandy34 

     

    Replace ORD_ID and MLSTN with the fields you want to get the maximum date for. Similarly you can add more fields.

    In CALCULATE , I am removing all filters and adding only those fielters for which I want to get the maximum date.

    This is a Calculated Column

    ------------------------------------

    Max Date by =
    VAR _Ord_ID = Orders[ORD_ID]
    VAR _MLSTN = Orders[MLSTN_CD]

    RETURN CALCULATE( MAX(Orders[RECEIVED DATE]), REMOVEFILTERS(), Orders[ORD_ID] = _Ord_ID && Orders[MLSTN_CD] = _MLSTN)
     

     

  • spandy34's avatar
    spandy34
    Responsive Resident
    2 years ago

    That has worked too! Thank you so much for your help.  Much appreciated.