Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    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

v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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

     

     

    • v-deddai1-msft's avatar
      v-deddai1-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        Thank you, this worked!! 

         

        Pjm