cancel
Showing results for
Did you mean:
Frequent Visitor

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

@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

1 ACCEPTED SOLUTION
Super User III

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)

3 REPLIES 3
Super User III

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)

Super User III

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

Why is 103 red not counted ?

Regards,

HN

Frequent Visitor

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 🙂

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.