Count of id ocurrences one period prior to the latest entry only.

[giordafrancis]

@harshnathani  added more details below to main post hope it's clearer
Consider the below table:

id	rag	date
101	red	01/06/2020
101	red	02/06/2020
103	red	01/06/2020
103	green	02/06/2020
104	amber	02/06/2020
103	green	05/06/2020
101	green	05/06/2020

I'm looking to create measures:

1- that tallies the count of all id except one submission date prior the latest date  (02/06/2020 in this case).

My code attempt for this would be, assuming above table named as table
```
count_prior_latest =
VAR temp_table = Filter(table, table[date] <> max(table[date])) -- remove max date, and create temp table
VAR second_max = max(temp_table[date])  -- second latest entry
Return Calculate(countrows(temp_table[id]) ,

                           Filter(temp_table, temp_table[date]  = second_max)

                           ) -- only count second latest

Desired outcome for 1 in a matrix (id vs rag)

count_pior_latest

	red	amber	green
101	1
103			1
104		1

Thank you in advance

[harshnathani · Accepted solution]

Hi @giordafrancis ,

Took me some time. But it was all worth the effort (I hope so 🙂 )

Use this measure

Second Largest =
VAR _max =
    CALCULATE (
        MAX ( 'Table8'[date] ),
        ALL ( 'Table8' )
    )
VAR _second =
    CALCULATE (
        MAX ( 'Table8'[date] ),
        FILTER (
            ALL ( Table8 ),
            Table8[date] < _max
        )
    )
RETURN
    CALCULATE (
        COUNT ( 'Table8'[id] ),
        FILTER (
            'Table8',
            'Table8'[date] = _second
        )
    )

I added some more data to check.

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[harshnathani]

Hi @giordafrancis ,

Not clear with the expected output, Can you pls explain a little better.

Why is 103 red not counted ?

Regards,

HN

[giordafrancis]

Hi @harshnathani ,
Thank you for your post.  the expected output only accounts for counts of id for 02/06. The second latest entry. If a date value of 06/06 was present on the date column the second latest entry would be 05/06.

There's no id, rag pair (103, red)  for date 02/06. Hope this is clear, if not let me know 🙂