Return last occurrence(DateTime) of certain status filtered by UserName

[govi]

Hi,

Can't figure this out:

I have a table(events) with 3 columns:

I want to return the DateTime of the last occurrence of the Status "Finished" per UserName.

Can anyone help?

Thanks!

govi

[themistoklis · Accepted solution]

@govi

Create a new Calculated column and not Measure and add the following formula:

Rank = 
VAR d = 'Table'[DateTime]  
VAR a = 'Table'[UserName]   
RETURN
    CALCULATE (
        RANK.EQ ( d, 'Table'[DateTime], DESC ),
        FILTER ( ALL ( 'Table' ), 'Table'[UserName]= a && 'Table'[Status]="Finished")
    )

Then create a new table object and on filter section put the rank field with value 1

[AlB · Accepted solution]

Hi @govi

Try this:

1. Place Events[UserName] in the rows of a matrix visual

2. Place this measure in values of the matrix:

LastOccurrence =
CALCULATE ( MAX ( Events[DateTime] ), Events[Status] = "Finished" )

[govi]

Thanks @AlB !

Also a very useful solution.

I will use the solution @themistoklis provided for now because it is nice to have the returns in a column

[jhd]

Hi @govi

I am trying to figure out something VERY similar...

To use your example, this is as far as I have got:

=CALCULATE(

LASTDATE(DateTime),
FILTER(ALLEXCEPT(NameOfYourTable,UserName),Status="Finished"))

However, as you are probably also experiencing, it doesn't work...

The ALLEXCEPT UserName appears to be ignored, and the last Finished date is returned regardless of UserName.

[themistoklis · Accepted solution]

@govi

Create a new Calculated column and not Measure and add the following formula:

Rank = 
VAR d = 'Table'[DateTime]  
VAR a = 'Table'[UserName]   
RETURN
    CALCULATE (
        RANK.EQ ( d, 'Table'[DateTime], DESC ),
        FILTER ( ALL ( 'Table' ), 'Table'[UserName]= a && 'Table'[Status]="Finished")
    )

Then create a new table object and on filter section put the rank field with value 1

[rooftops]

Hello! Hate to gravedig, but I'm getting a weird error with this forumla. It's ranking my entries in proper date order, and skipping *almost all* of my filter, but seems to be pushing the rank to lines with the same date. My running theory is that the formula sees that they're the same date (text) and just borks but I have no idea what I'm doing so who knows!

[govi]

Thanks @themistoklis

This is what I am looking for!