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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.