cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:

idragdate
101red01/06/2020 
101red02/06/2020 
103red01/06/2020 
103green02/06/2020 
104amber02/06/2020 
103green05/06/2020 
101green05/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
1011  
103        1
104 1 

 

Thank you in advance

 

 



1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

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

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.

 

1.jpg2.JPG

 


Regards,

Harsh Nathani


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


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

View solution in original post

3 REPLIES 3
Highlighted
Super User V
Super User V

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

Hi @giordafrancis ,

 

 

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

 

Why is 103 red not counted ?

 

Regards,

HN

Highlighted
Frequent Visitor

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

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 🙂

Highlighted
Super User V
Super User V

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

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.

 

1.jpg2.JPG

 


Regards,

Harsh Nathani


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


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

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors