Dynamic data filtering based on selected date

[WannaBe]

Hello,

I have 2 tables linked by a date field.
#1 - Calendar = fields(Date)

#2 - UserActivity = fields (StartDate, Email)

Calendar table go from 2021-01-01 until 2024-07-31.
I need with a date slicer choose the period that I want and from there I need to apply the following logic.

-> Given at filtered dates A to B, find user’s (Email) most recent use and go back 12 months to count all uses per user.
-> If count is equal or higher than 3, flag these user with a true flag.
-> having a count of all users that have the flag identify as true.
Hope someone can help on that one.

Tank you!

[WannaBe]

Thank you very much @Anonymous .
This helps a lot, but I'm still having trouble getting exactly what I'm looking for.
The highlighted row for example shows 1 as "UseLast12" and when I click on that row we see 4 as "CountUser".
This seems true.
In the date range used, it has accessed 1 time and 4 in total in the last 12 months.
However I would like to not have to click on each of the rows in order to have the total for the last 12 months. I would like to have the grand total of everyone for their usage in the last 12 months.
So in the example have 4 for the last 12 months and not 1.
Do you have any idea how I can get this number?

[Anonymous · Accepted solution]

Hi @WannaBe ,

I create two tables as you mentioned.

Calendar = CALENDAR(DATE(2021,1,1),DATE(2024,7,31))

Then I create two measures and here is the DAX code.

LastUseDate =
CALCULATE (
    MAX ( 'Table'[StartDate] ),
    FILTER (
        'Table',
        'Table'[StartDate] >= MIN ( 'Calendar'[Date] )
            && 'Table'[StartDate] <= MAX ( 'Calendar'[Date] )
    )
)

UseLast12 = 
CALCULATE(
    COUNT('Table'[Email]),
    FILTER(
        'Table',
        'Table'[StartDate] <= [LastUseDate] &&
        'Table'[StartDate] > EDATE([LastUseDate], -12)
    )
)

Finally I get this measure and get what you want.

CountUser = 
VAR _User = IF('Table'[UseLast12] >= 3,"T","F")
RETURN
CALCULATE(
    COUNTROWS(
        FILTER(
            'Table',
            _User = "F"
        )
    )
)

Best Regards

Yilong Zhou

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