cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Creating Measure for earliest date that appears more than 1 time

Hello! 

 

Looking to create a measure that returns the earliest date that appears multiple times in an event table. I am struggling to filter the event table in a way where only rows where the date has shown up multiple times remain. The following has not worked properly for me: 

Earliest Date = CALCULATE(MIN('Event Table'[Date]), FILTER(ALL('Event Table'), COUNT('Event Table'[Date]) > 1))
 
What I need is a measure that returns the first date where the count of the date is > 1... so the first date in March below, instead of the one in Jan/Feb timeframe. 
 pbi - earliest date help.PNG
 
Thanks, 
Pjm 
 
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Creating Measure for earliest date that appears more than 1 time

Hi,

 

Would you please tell me if you want to calcaulate the earliest install date for each device?

 

Try this:

 

Install Date =
CALCULATE (
    MIN ( 'Table'[Date] ),
    FILTER ( ALL ( 'Table'[Date] ), CALCULATE ( COUNT ( 'Table'[Date] ) > 1 ) )
)

 

Best Regards,

Dedmon Dai

View solution in original post

6 REPLIES 6
Highlighted
Super User III
Super User III

Re: Creating Measure for earliest date that appears more than 1 time

Hi @pjm2 

 

Try this Measure

Measure = 
CALCULATE(
    MIN( 'Table'[Date] ),
    FILTER(
        ALL( 'Table'[Date] ),
        COUNTROWS( 'Table' ) > 1
    )
)

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Highlighted
Microsoft
Microsoft

Re: Creating Measure for earliest date that appears more than 1 time

Hi @pjm2 ,

 

Would you please try the following measure:

Earliest Date =

CALCULATE (

    MIN ( 'Event Table'[Date] ),

    FILTER (

        ALL ( 'Event Table' ),

        CALCULATE ( COUNT ( 'Event Table'[Date] ) > 1 )

    )

)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

Highlighted
Regular Visitor

Re: Creating Measure for earliest date that appears more than 1 time

Hi @Mariusz

 

Unfortunately this measure still gave the earliest date even if it only appears once in the table. 

 

Any other thoughts? 

 

Pjm  

Highlighted
Regular Visitor

Re: Creating Measure for earliest date that appears more than 1 time

Hi @v-deddai1-msft , 

 

This measure returned a date that is not the correct date (earliest date with a count of >1) and is not the earliest date with a count of 1 or greater. 

 

I should have included this to begin... 'I am trying to find the earliest date with a count of >1 for several different devices. I have a table visual were column 1 has all the devices and column 2 is the "Install Date" measure. When I use what you have proposed:

 

Install Date = CALCULATE(MIN('Table'[Date]), FILTER(ALL('Table'),CALCULATE(COUNT('Table'[Date]) > 1)))
 
the install date for all of the devices is the same. I need it to be different for each device, as they all were installed on different dates. 
 
Pjm

 

 

Highlighted
Microsoft
Microsoft

Re: Creating Measure for earliest date that appears more than 1 time

Hi,

 

Would you please tell me if you want to calcaulate the earliest install date for each device?

 

Try this:

 

Install Date =
CALCULATE (
    MIN ( 'Table'[Date] ),
    FILTER ( ALL ( 'Table'[Date] ), CALCULATE ( COUNT ( 'Table'[Date] ) > 1 ) )
)

 

Best Regards,

Dedmon Dai

View solution in original post

Highlighted
Regular Visitor

Re: Creating Measure for earliest date that appears more than 1 time

Thank you, this worked!! 

 

Pjm 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors