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
Community Champion
Community Champion

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

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

Upcoming Events

Upcoming Events

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

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.