topic Count of id ocurrences one period prior to the latest entry only. in DAX Commands and Tips

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

giordafrancis — Tue, 30 Jun 2020 08:38:42 GMT

Anonymous 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

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

Anonymous — Mon, 29 Jun 2020 19:50:12 GMT

Hi giordafrancis ,

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

Why is 103 red not counted ?

Regards,

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

giordafrancis — Mon, 29 Jun 2020 20:12:10 GMT

Hi Anonymous ,
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 🙂

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

Anonymous — Tue, 30 Jun 2020 11:23:21 GMT

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!