Getting first date but adding a measure filter

[cwollett]

Let's say I have website click data where I know the URL, date of clicks (and the week's start date by association), and number of clicks. I want to have a calculation in my data that includes the first accessed date for that website. It should look like:

Website	Week Start Date	Clicks	First Accessed Date
website.com/A	7/7/2024	12	7/7/2024
website.com/A	7/14/2024	24	7/7/2024
website.com/B	7/14/2024	10	7/14/2024

I have tried to use the following measure:

CALCULATE(
    MIN('Date Table'[Week Start Date]),
    REMOVEFILTERS('Date Table'[Week Start Date])
)

However, that gives me dates all the way back to the beginning of my date table:

Website	Week Start Date	Clicks	First Accessed Date
website.com/A	6/30/2024		6/30/2024
website.com/B	6/30/2024		6/30/2024
website.com/A	7/7/2024	12	6/30/2024
website.com/B	7/7/2024		6/30/2024
website.com/A	7/14/2024	24	6/30/2024
website.com/B	7/14/2024	10	6/30/2024

If I try to add a filter with clicks, I get the following:

CALCULATE(
    MIN('Date Table'[Week Start Date]),
    REMOVEFILTERS('Date Table'[Week Start Date]),
    FILTER('Date Table', [Clicks] > 0)
)

Website	Week Start Date	Clicks	First Accessed Date
website.com/A	6/30/2024		6/30/2024
website.com/B	6/30/2024		6/30/2024
website.com/A	7/7/2024	12	7/7/2024
website.com/B	7/7/2024		7/7/2024
website.com/A	7/14/2024	24	7/14/2024
website.com/B	7/14/2024	10	7/14/2024

[Anonymous · Accepted solution]

Hi @cwollett ,

Great try. I understand that you want to group by URL and return the oldest date for each URL. If that's the case, you can try:

MEASURE =
CALCULATE (
    MIN ( 'Date Table'[Week Start Date] ),
    FILTER (
        ALL ( 'Table of Website' ),
        [Website] = MAX ( 'Table of Website'[Website] )
    )
)

"Table of Website" is the name of the table where your Website is located.

Best Regards,
Stephen Tao

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