cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
giordafrancis
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
harshnathani
Super User III
Super User III

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
harshnathani
Super User III
Super User III

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

harshnathani
Super User III
Super User III

Hi @giordafrancis ,

 

 

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

 

Why is 103 red not counted ?

 

Regards,

HN

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 🙂

Helpful resources

Announcements
MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors