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

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

August Community Highlights

Check out a full recap of the month!

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

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.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors