Countrows last date

[youconnect]

I have this table

id	state	date
1	active	01/01/2020
1	reserved	05/01/2020
1	suspended	08/01/2020
1	sold	15/01/2020
2	active	02/01/2020
2	suspended	03/01/2020
2	active	05/01/2020
3	active	05/01/2020
4	active	06/01/2020
4	sold	08/01/2020

I want count rows of distinct IDs where last date equal to "active"

Result expected is: 2 (last date from these 2 IDs have the state "active".
Can you help me?

Thanks

[edhans]

This will do what you describe, but it is 1, not 2.

Latest Record Count = 
VAR LatestDate = 
CALCULATE(
    LASTDATE('Sample Data'[Date]),
    'Sample Data'[state] = "active"
)
VAR ActiveIDCount = 
CALCULATE(
    DISTINCTCOUNT('Sample Data'[id]),
    FILTER(
        'Sample Data',
        'Sample Data'[Date] = LatestDate
    )
)
RETURN
COALESCE(ActiveIDCount,0)

the last date of an active item is Jan 6, 2020, and only one ID has that date.

How are you getting 2?

[youconnect]

@edhans 

No.
last date for id 1 is 15/01/2020 and state is not active, not count

last date for id 2 is 05/01/2020 and state is active, count

last date for id 3 is 05/01/2020 and state is active, count

last date for id 4 is 08/01/2020 and state is not active, not count.

So count is 2

[V-lianl-msft · Accepted solution]

Hi @youconnect ,

I am understanding your logic and have made the following calculation:

count_active_id = 
var last_active_date = CALCULATE(MAX(Sheet2[date]),FILTER(ALLEXCEPT(Sheet2,Sheet2[id],Sheet2[date]),Sheet2[state]="active"))
var last_date = CALCULATE(MAX(Sheet2[date]),ALLEXCEPT(Sheet2,Sheet2[id]))
return CALCULATE(COUNTROWS(FILTER(Sheet2,last_active_date=last_date)))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[youconnect]

Thanks @V-lianl-msft it worked like a charm.

Another question: If I want only the records before 01-01-2020 how do that?

[edhans]

I suspect there is an cleaner way to do this, but this does the trick.

Counting Measure = 
VAR FirstTable =
    ADDCOLUMNS (
        SUMMARIZECOLUMNS ( 'Sample Data'[id] ),
        "Date2", LASTDATE ( 'Sample Data'[Date] )
    )
VAR CombinedTable =
    NATURALINNERJOIN (
        'Sample Data',
        firsttable
    )
VAR RowCount =
    COUNTROWS (
        FILTER (
            FILTER (
                CombinedTable,
                [Date] = [Date2]
            ),
            [state] = "active"
        )
    )
RETURN
COALESCE( RowCount, 0)

[youconnect]

Thanks @edhans but it's not working. Higher results expected.