Date value based off MAX date of a different column

[Mtelf24]

I have a table of values that measure date change events linked to a unique id, e.g.

ID	Record Created	ChangedFrom	ChangedTo
1	20/05/2020	30/06/2020	13/07/2020
1	21/05/2020	13/07/2020	20/07/2020
1	28/05/2020	20/07/2020	28/06/2020
2	14/04/2020	16/05/2020	15/09/2020
2	18/05/2020	15/09/2020	23/09/2020
2	01/06/2020	23/09/2020	12/09/2020

I need to measure the earliest movement based off ID, and the latest movement based off ID too.

Before I was using a formula like:

LatestMovement = 
CALCULATE (
    MAX ( Table[ChangedTo] ),
    ALLEXCEPT ( Table, Table[ID] )
)

And that works fine to a certain extent - but in my above table isn't quite what I'm after. It would return 20/07/2020 for all three rows for ID 1 (it being in all three rows suits me fine) but I don't want that returning because the latest movement is actually 28/06/2020 based off the created date.

My question is, how do I return the max date of ChangedTo in the results in the style of the above formula, but using the MAX of the created on date? I.E. the formula should be able to check for the MAX of the createdon date based off the ID and then return the value in the column for the ID, e.g:

ID	Record Created	ChangedFrom	ChangedTo	LatestMovement
1	20/05/2020	30/06/2020	13/07/2020	28/06/2020
1	21/05/2020	13/07/2020	20/07/2020	28/06/2020
1	28/05/2020	20/07/2020	28/06/2020	28/06/2020
2	14/04/2020	16/05/2020	15/09/2020	12/09/2020
2	18/05/2020	15/09/2020	23/09/2020	12/09/2020
2	01/06/2020	23/09/2020	12/09/2020	12/09/2020

I've seen a number of other threads on this sort of topic but can't quite land on what I'm looking for.

[MFelix]

Hi @Mtelf24 ,

Looking at the final result you present you want the minimum value not the MAX so just use:

LatestMovement = 
CALCULATE (
    MIN ( Table[ChangedTo] ),
    ALLEXCEPT ( Table, Table[ID] )
)

But I assume this is only a coincidence of having the lowest date at the highest creation date.

Try the following measure:

LatestMovement =
)
LOOKUPVALUE (
    'Table'[Changed To],
    'Table'[Record Created], CALCULATE (
        MAX ( 'Table'[Record Created] ),
        ALLEXCEPT ( 'Table', 'Table'[ID] )
    ),
    'Table'[ID], SELECTEDVALUE ( 'Table'[ID] )
)

This  should give expected result. I have made a change to the date of the record 1 and got the date for the last line, so assuming that is the result you need.

[Mtelf24]

Hi @MFelix, thanks for your help on this.

(apologies if my post below isn't clear - the board automation cleared out my original post so I had to rewrite it)

I've tried the secondary formula you've provided, although unfortunately without success - no error returned, but all rows in the column are blank (which searching, seems to be a common issue for PowerBI users unfamiliar with the formula) which I think is suggesting its not getting a match? For what its worth, all field references (other than the ID) for changedto and created are of the data type 'date' so I don't think its a data type thing - although I did try changing the createddate to a date/time type since I noticed the output of just using the CALCULATE part of your formula (which works fine on its own) within the LOOKUPVALUE returned a date/time format in the output, e.g:

LatestMovementLookup = CALCULATE ( MAX ( Table[Record Created] ), ALLEXCEPT ( Table, Table[ID] ) )

To me it looks like your formula should work (i.e. it will return whatever is 'Changed To' in the row where 'Record Created' matches the output of the CALCULATE formula (which is returning the latest (max) 'Record Created' value by ID), but I must be missing something in regards to something along the lines of data type formatting or duplicate references?

ID	'Table'[Record Created] // below is output after changing format from Date to Date Type using example of 5 different rows with the same ID	Changed To	CALCULATE ( MAX ( 'Table'[Record Created] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) //output of above formula below
1	19/03/2020 00:00:00	04 May 2020	27/05/2020 00:00:00
1	27/05/2020 00:00:00	26 May 2020	27/05/2020 00:00:00
1	19/03/2020 00:00:00		27/05/2020 00:00:00
1	16/04/2020 00:00:00	25 April 2020	27/05/2020 00:00:00
1	27/05/2020 00:00:00	26 May 2020	27/05/2020 00:00:00

I'm wondering if its having an issue with the fact that the original record created field doesn't record the time, in which case its getting confused by the two 27/05/2020 00:00:00 references when doing LOOKUPVALUE(), I think I do have a method for getting the exact time rather than 00:00:00 so could try that.

(Changed to can be blank by the way as the above is basically like an audit record, and a user can remove a date as much as change it to a new one)

I'm just having abit of a look on the official microsoft reference on LOOKUPVALUE() and this does appear to be along the right track of what I'm looking to achieve. I can't quite work out what the

,Table[ID], SELECTEDVALUE ( Table[ID] ) bit you added is for though?

I get a blank result if I remove the above as well without an error.

[MFelix · Accepted solution]

Hi @Mtelf24 ,

I have looked at the data you send out and you should use the following measure:

LatestMovement_ = 
VAR TEMP_TABLE =
    FILTER (
        SUMMARIZE (
            ALL ( Sheet1[ID]; Sheet1[CreatedOn]; Sheet1[ChangedTo] );
            Sheet1[ID];
            Sheet1[CreatedOn];
            Sheet1[ChangedTo]
        );
        Sheet1[ID] = SELECTEDVALUE ( Sheet1[ID] )
    )
VAR DATE_SELECT =
    MAXX ( TEMP_TABLE; Sheet1[CreatedOn] )
VAR DATE_CHANGED_TO =
    MAXX (
        FILTER ( TEMP_TABLE; Sheet1[CreatedOn] = DATE_SELECT );
        Sheet1[ChangedTo]
    )
RETURN
    IF ( DATE_CHANGED_TO = BLANK (); ""; DATE_CHANGED_TO )

[Mtelf24]

Apologies, just to add to MFelix's v.helpful solution (which requires use of a measure instead of calculated column), I also wanted to add that if I changed the last line to avoid a data type conflict:

IF ( DATE_CHANGED_TO = BLANK (); ""; DATE_CHANGED_TO )

to

IF ( DATE_CHANGED_TO = BLANK (); BLANK(); DATE_CHANGED_TO )

 I could then make use of it as a calculated column with the following (though I haven't fully explored any 'gotchas' with it yet that may cause issues as it seems almost too easy), but its looking good for what I wanted to use it in so far (matrix) - as I found that when I added the measure to a table the table would not filter 100% correctly when selecting a column/value in my matrix:

LatestMoveCalcColumn = 
CALCULATE(LDOSMovementsCOVID[LatestMovement_])

[MFelix]

Hi @Mtelf24 ,

Just to clarify why I have placed "" instead of BLANK in the measure, is that when you are calculating measure blank values get automatically filter out from the visualization so if you had a certain item that the value of the maximum date was returning BLANK you would not had any result in the visualization.

But great catch.

[MFelix]

Hi @Mtelf24 ,

In the test file I have made this worked correctly with date fields, no issue so not really sure if it's a date format issue.

Can you share a mockup file please? If there is sensitive information share trough private message.

The SELECTEDVALUE part is to force the filter to pickup the ID that is on the current row, as you can we are making a Lookupvalue on the full table so with the selected value we only focus on the specific ID.