Forum Discussion

silverdale9999's avatar
silverdale9999
Icon for Helper I rankHelper I
6 years ago
Solved

Maximum Date Custom Column - Compare 2 dates in different tables

Hi everyone!

 

Trying to get my head around this and not getting very far!

 

I have 2 tables - Problem, and Task.  The two are related via Problem ID.  Each Problem will have multiple tasks.

 

Problem contains:

Problem ID

Problem Last Modified Date

 

Task Contains

Problem ID

Task ID

Task Last Modified Date

 

Each row in Problem is unique, ie one Problem per row.

Tasks will have around 4 rows per problem, each with a potentially different last modified date.

 

What I want to achieve is:

 

Compare the Problem Last Modified date with the Maximum Task Last Modified date.  Whichever is greater gets populated into the custom column within the Problem Table.  Possibly explained this really badly, but hopefully this pic will help illustrate what I mean:

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    silverdale9999 - Should be:

    Column =
  VAR __Other = MAXX(RELATEDTABLE('Table2'),[Task Last Modified Date])
RETURN
  MAXX( {[Last Modified Date], __Other },[Value])

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    silverdale9999 - Should be:

    Column =
  VAR __Other = MAXX(RELATEDTABLE('Table2'),[Task Last Modified Date])
RETURN
  MAXX( {[Last Modified Date], __Other },[Value])
    • silverdale9999's avatar
      silverdale9999
      Icon for Helper I rankHelper I
      6 years ago

      That works perfectly ! Thanks so much for taking the time to reply.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

    There might be a better way to do this but at the top of my head this works.

     

    Update_Date =

    VAR _new = CALCULATE(MAX('TaskTable'[TaskUpdateDate]),RELATEDTABLE(ProblemTable))

    Return

    if (_new < 'ProblemTable'[ProblemModifiedDate],'ProblemTable'[ProblemModifiedDate],_new)
     

    You can add isblank() if you need.