Forum Discussion
Getting first date but adding a measure filter
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 |
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
1 Reply
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