topic Count of unique customer ID within date range - custom column? in DAX Commands and Tips

Count of unique customer ID within date range - custom column?

kate_wass — Wed, 09 Mar 2022 09:33:10 GMT

I would love any advice to help me solve my problem statement. As a relatively new person to Power BI I would love any advice you can provide. I am looking to count on a record fort a call received today how many calls did that customer maki in the previous 7 days. I have a dataset of calls over time, sample included below. The unique customer ID is the phone number, the date range is the start date of the call minus 7 days.

I think I want to solve this with a custome column, but open to any advice. I am unsure whther the fact that my start date column includes time could be causing me issues? Idealy I would want a solution that can consider the time of call as the customer may call multiple times in one day.

For the sample I have provided below, I have provided the results I would expect for the calls received on the 08/01/22 based on the data supplied 01/01/22 - 08/01/22.

Thanks in advance for any support you can provide me.

Start date / time	Phone number	How many calls in previous 7 days
1/01/2022 8:00	12345
1/01/2022 9:00	45678
1/01/2022 10:00	12345
2/01/2022 10:00	12345
2/01/2022 11:00	45678
3/01/2022 8:00	12345
4/01/2022 8:00	45678
4/01/2022 9:00	45678
4/01/2022 10:00	12345
4/01/2022 11:00	12345
5/01/2022 9:00	45678
5/01/2022 10:00	12345
6/01/2022 10:00	12345
7/01/2022 11:00	12345
8/01/2022 8:00	98765	0
8/01/2022 9:00	12345	9
8/01/2022 10:00	45678	5
8/01/2022 11:00	45678	6

Re: Count of unique customer ID within date range - custom column?

Jihwan_Kim — Wed, 09 Mar 2022 09:53:46 GMT

Hi,

I suggest having date column and time column separate.

And please wite the below calculated column to create a new column.

It will show the result for counting calls during previous seven days only for the dates that are after 8th Jan. 2022.

Please also check the attached pbix file.

Calls in previous 7 days CC = VAR currentphonenumber = Data[Phone number] VAR currentdate = Data[Start date] VAR sevendaysago = currentdate - 7 RETURN IF ( Data[Start date] >= DATE ( 2022, 1, 8 ), COUNTROWS ( FILTER ( Data, Data[Phone number] = currentphonenumber && Data[Start date] >= sevendaysago && Data[Start date] < currentdate ) ) + 0 )

Re: Count of unique customer ID within date range - custom column?

johnt75 — Wed, 09 Mar 2022 10:01:55 GMT

This will calculate the number of calls in the previous 7 days

Num prev calls 7 days = 
var currentPhone = 'Table'[Phone number]
var currentDate = 'Table'[Start date / time]
return COALESCE( CALCULATE( COUNTROWS('Table' ), REMOVEFILTERS(), 'Table'[Phone number] = currentPhone 
&& 'Table'[Start date / time] < currentDate && 'Table'[Start date / time] >= currentDate - 7 ), 0 )

Please note that this does take into account the time of the call, so the numbers returned are slightly different from your example. e.g. the last call for number 12345 occurs at 9am, so the first call on 1/1 at 8am is not included in the count as it falls outside the full 7 days.

If you want to include all calls which occurred upto 7 days ago, regardless of the time of the call, you could split your start date time column into separate date and time columns, or you could change the new column definition to just get the date part of currentDate - 7.

Re: Count of unique customer ID within date range - custom column?

CNENFRNL — Wed, 09 Mar 2022 11:20:17 GMT

Excel worksheet formula is way powerful to solve such a simple question,