Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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
v-deddai1-msft
Community Support
Community Support

Hi @Anonymous ,

 

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

Anonymous
Not applicable

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

 

 

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

Anonymous
Not applicable

Thank you, this worked!! 

 

Pjm 

Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

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

 

Anonymous
Not applicable

Hi @Mariusz

 

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

 

Any other thoughts? 

 

Pjm  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.