How to count frequency of occurrences in the last 7 days when data has date/time

Question

Here is the sample dataset, having 2 columns, Data, and Name

The problem is to create a measure that is going to count the number of name occurrences having a count greater than 2

Step 1 is to find the Names that show up at least once a day without a timestamp

Step 2 is to find names that have occurrences of more than 2 from the output got of Step 1:

Step 3: is to get the distinct count of names from Step 2:

Expected Output = 2

Not sure how to do this in DAX.

Note: I m looking for a measure that gives the Output = 2 and not the steps, the steps are for understanding purposes/ or to provide clarity

Answer 1

First write a measure to count the names

Measure = CALCULATE(COUNT(Data[Name]), FILTER(ALLEXCEPT(data, Data[Name]), Data[Date] <= DATEVALUE("2023-05-07")))

Then use that measure to count names more than 2

Measure 2 = CALCULATE(DISTINCTCOUNT(Data[Name]), FILTER(data, [Measure] > 2))

Answer 2

If you either split your date and time column into separate columns or create them in your fact table, then this is trivial using FILTER and COUNTROWS() and maybe RELATEDTABLE().

Answer 3

Testing for updated list:

I basically did this:

Measure = 
var base = 
SUMMARIZE(data,Data[Name],"Count",count(Data[Date]))
return calculate( countrows(filter(base,[Count] >2)))

which gives the output: Sam(8), Tim(3), Ahmed(3), Jake(4), Oscar(3)

Then to get the count of frequent names in the last 7 days I use the below measure:

Measure 2 = 
var last_7 = now() - 7
return calculate( [Measure], filter(Data, Data[Date] >= last_7))

The result is shown below: Checks for count more than 2 times in the last 7 days (that is starting 5/5/2023), The result is 2, because the counts>2 are Sam(5) and Oscar(3).