Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Retrieve Last Date a Value Changed by Group

Hi everyone,

 

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

GroupDateSetting
A1/30/235.3
A1/15/235.3
A1/5/234.7
B1/30/233.2
B1/29/232.7
B1/7/232.4
C1/28/236.1
C1/17/236.1
C1/1/235.2

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

GroupMost Recent SettingDate of Last Change
A5.31/15/23
B3.21/30/23
C6.11/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

 

"Need Help"
last change
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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]
    )

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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]
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.