How to count duplicates within date range as applied through slicers

[Anonymous]

In a list of DateTime, phone numbers, I need to find out dupes of phone numbers based on user applied filters/slicers against date range.

For exmaple, if user selected date range from 1st Jan 2021 to 31st Dec 2021, I need to count dupes of phone numbers with this date range. As and when user change date range, dupes count need to change please.

Appreciate your help. Thanks 

[Ashish_Mathur]

Hi,

Create a calculated column formula to extract Date from the Date/Time column

Date = int(Data[DateTime])

Create a Calendar Table and write calculated column formulas to exteact Year, Month name and Month number.  Sort the Month name by the month number.  To your visual, drag Year and Month name from the Calendar Table and select any one Year and Month name.  Write these measures:

Phone number count = counta(Data[Phone])

Measure 1 = countrows(filter(values(Data[Phone]),[Phone number count]>1))

Hope this helps.

[varunsabu]

Hi @Anonymous 

If your data is in the below format

You can extract the date column in power query as follows(You can achieve this using DAX formula too)

Select Date Time column -> go to Add Column ribbon -> Date -> Date Only

In Power BI desktop create a measure as follows

It will return the number of times a phone number is duplicated for the selected date range.

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

[Anonymous]

Unfortunatly, its showing just a [Total Count of Phone]-1, but I wanted to know number of dupes within slicers applied

[amitchandak · Accepted solution]

@Anonymous , You can create a date field , use that in slicer or join it with date table and use slicer from date table

DateTime = datevalue([DateTime])

You can have measures

M1= count(Table[Phone Numbers])

Duplicate = countx(filter(values(Table[Phone Numbers]) , [M1] >1 ), [Phone Numbers])

[Anonymous]

Unfortunatly, dupes being calculated based on entire dataset. But I wanted dupes within selected date range