topic Re: Retrieve Last Date a Value Changed by Group in DAX Commands and Tips

Retrieve Last Date a Value Changed by Group

Anonymous — Mon, 30 Jan 2023 21:54:53 GMT

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

Re: Retrieve Last Date a Value Changed by Group

FreemanZ — Mon, 30 Jan 2023 23:53:04 GMT

hi Anonymous

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]

)

Re: Retrieve Last Date a Value Changed by Group

Anonymous — Tue, 31 Jan 2023 15:42:33 GMT

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.