Retrieve Last Date a Value Changed by Group

[TheQ]

Hi everyone,

I've been troubleshooting this issue for a couple hours now to no avail. Let's say I have the following dataset:

Group	Date	Setting
A	1/30/23	5.3
A	1/15/23	5.3
A	1/5/23	4.7
B	1/30/23	3.2
B	1/29/23	2.7
B	1/7/23	2.4
C	1/28/23	6.1
C	1/17/23	6.1
C	1/1/23	5.2

I want to know the date of the most recent value change in this form:

Group	Most Recent Setting	Date of Last Change
A	5.3	1/15/23
B	3.2	1/30/23
C	6.1	1/17/23

I have tried a few variations of this as a Calculated Column:

Date of Last Change =
VAR _Setting = SUM( Table[Setting] )
VAR _Date = Table[Date]
RETURN
CALCULATE(
     MAX( Table[Date] ),
     ALLEXCEPT( Table, Table[Group] ),
     Table[Date] < _Date,
     SUM( Table[Setting] ) <> _Setting
)

This usually gives me the "The true/false expression does not specify a column" error. I was able to get past this by replacing the commas with &&, but end up with blanks for the whole column.

Thanks in advance for the help!
Q

[FreemanZ · Accepted solution]

hi @TheQ 

try like:

NewColumn =

VAR _group = [Group]

VAR _grouptable = 

FILTER(

     TableName, 

     TableName[Group] = _group

)

VAR _lastdate =

MAXX(

    _grouptable,

   TableName[Date]  

)

VAR _lastsetting =

MAXX(

    FILTER(

         _grouptable,

         TableName[Date]=_lastdate

    ),

   TableName[Setting]  

)

RETURN

MINX(

    FILTER(

         _grouptable,

         TableName[Setting] = _lastsetting

    ),

    TableName[Date]

)

[TheQ]

Thanks @FreemanZ ! That amazingly worked. Do you have any idea why this worked and my original method didn't? I've used my original method successfully on a similar case but where the "Setting" wasn't a number, but a "Passed"/"Failed" column.