Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
bipowerbix
Helper I
Helper I

Getting count of re-occurring values within the same table for dates instead of date/time

Consider the table with 2 columns: Date and Name, Date is in Date Time Format.

Date Name
5/1/2023 10:00  Sam
5/1/2023 18:00  Sam
5/1/2023 8:00  Tim
5/2/2023 9:00  Jake
5/2/2023 11:00  Jake
5/3/2023 0:00  Ahmed
5/3/2023 7:00  Woo
5/3/2023 4:00  Sam
5/4/2023 17:00  Jake
5/5/2023 14:00  Oscar
5/5/2023 6:00  James
5/6/2023 9:00  Jake
5/6/2023 10:00  Sam
5/7/2023 5:00  Tim

 

 

Want to get the total Count of Names that appear more than 2 times in the last 7 days? (May 7 to May 1) in this example dataset.
If the name appears twice on the same day, then it should be considered as 1 (Image of the resulting Dataset shown below),

Note: This image shows the result dataset, it's not the output.  The Output is 2

 

bipowerbix_0-1683752323212.png

 

 

 

Expected Output: 2 (Since Sam and Jake appear more than two times in the 7-day timeframe)

 






1 ACCEPTED SOLUTION
bipowerbix
Helper I
Helper I

Solved it using the below codes

 

 

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

 

 

and

 

 

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

 

 

 

Basically, grouping Names with day counts in step 1, which is defined by "Measure", then filtering out the results of "Measure" for the last 7 days in "Measure 2"


below is test dataset:
DateName

5/1/2023 10:00:00 AMSam
5/1/2023 6:00:00 PMSam
5/1/2023 8:00:00 AMTim
5/2/2023 9:00:00 AMJake
5/2/2023 11:00:00 AMJake
5/3/2023 12:00:00 AMAhmed
5/3/2023 7:00:00 AMWoo
5/3/2023 4:00:00 AMSam
5/4/2023 5:00:00 PMJake
5/5/2023 2:00:00 PMOscar
5/5/2023 6:00:00 AMJames
5/6/2023 9:00:00 AMJake
5/6/2023 10:00:00 AMSam
5/7/2023 5:00:00 AMTim
5/8/2023 7:00:00 AMSam
5/8/2023 8:00:00 AMAhmed
5/9/2023 6:00:00 AMSam
5/9/2023 11:00:00 AMOscar
5/9/2023 3:00:00 AMSam
5/10/2023 1:00:00 AMTim
5/11/2023 9:00:00 AMAhmed
5/11/2023 8:00:00 AMSam
5/12/2023 10:00:00 AMOscar

 

 

Result: 5 Users with more than 2 count (Sam(8), Tim(3), Ahmed(3), Jake(4), Oscar(3)) 
and 2 in the last 7 days since 5/5/23 (Sam(5) and Oscar(3))

bipowerbix_0-1683926235509.png

 

View solution in original post

4 REPLIES 4
bipowerbix
Helper I
Helper I

Solved it using the below codes

 

 

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

 

 

and

 

 

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

 

 

 

Basically, grouping Names with day counts in step 1, which is defined by "Measure", then filtering out the results of "Measure" for the last 7 days in "Measure 2"


below is test dataset:
DateName

5/1/2023 10:00:00 AMSam
5/1/2023 6:00:00 PMSam
5/1/2023 8:00:00 AMTim
5/2/2023 9:00:00 AMJake
5/2/2023 11:00:00 AMJake
5/3/2023 12:00:00 AMAhmed
5/3/2023 7:00:00 AMWoo
5/3/2023 4:00:00 AMSam
5/4/2023 5:00:00 PMJake
5/5/2023 2:00:00 PMOscar
5/5/2023 6:00:00 AMJames
5/6/2023 9:00:00 AMJake
5/6/2023 10:00:00 AMSam
5/7/2023 5:00:00 AMTim
5/8/2023 7:00:00 AMSam
5/8/2023 8:00:00 AMAhmed
5/9/2023 6:00:00 AMSam
5/9/2023 11:00:00 AMOscar
5/9/2023 3:00:00 AMSam
5/10/2023 1:00:00 AMTim
5/11/2023 9:00:00 AMAhmed
5/11/2023 8:00:00 AMSam
5/12/2023 10:00:00 AMOscar

 

 

Result: 5 Users with more than 2 count (Sam(8), Tim(3), Ahmed(3), Jake(4), Oscar(3)) 
and 2 in the last 7 days since 5/5/23 (Sam(5) and Oscar(3))

bipowerbix_0-1683926235509.png

 

FreemanZ
Super User
Super User

hi @bipowerbix 

try to plot a measure like:

 

Measure = 
VAR _list =
    ADDCOLUMNS(
        VALUES(data[Name]),
        "Count",
        CALCULATE(
            COUNTROWS(
                FILTER(
                    data, 
                    data[Date]>=DATE(2023,5,1)
                        &&data[Date]<=DATE(2023,5,7)
                )
            )
        )
    )
RETURN
    COUNTROWS(
        FILTER(
            _list, 
            [Count]>2
        )
    )

 

 verified with enriched data like:

DateName
5/1/2023 10:00Sam
5/1/2023 18:00Sam
5/1/2023 8:00Tim
5/2/2023 9:00Jake
5/2/2023 11:00Jake
5/3/2023 0:00Ahmed
5/3/2023 7:00Woo
5/3/2023 4:00Sam
5/4/2023 17:00Jake
5/5/2023 14:00Oscar
5/5/2023 6:00James
5/6/2023 9:00Jake
5/6/2023 10:00Sam
5/7/2023 5:00Tim
5/8/2023 5:00Tim
5/9/2023 5:00Tim
5/9/2023 5:00Tim

it worked like:

FreemanZ_0-1683770652339.png

I wanted to get it for the last 7 days, I have used the below-listed code instead od 5/1/2023 and 5/7/2023, I get an error saying 

"DAX comparison operations do not support comparing values of type Text with values of type Integer.  Consider using the VALUE or FORMAT function to convert one of the values"

 

 

 

data[date] >= (date(year(now()),month(now()),day(now())) -7 )

 

 

 

hi @bipowerbix 

what is the data type of data[date]?

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.