Calculate daily new user count within relative period

[Jinyang00]

This is raw data - daily sales data with Order date, Order ID. 

Order date	ID
12/25/2023	Q
12/25/2023	X
12/26/2023	A
12/27/2023	Y
12/28/2023	Z
12/29/2023	W
12/30/2023	P
12/31/2023	A
12/31/2023	J
12/31/2023	N
1/1/2024	A
1/1/2024	B
1/2/2024	N
1/2/2024	D
1/2/2024	F
1/3/2024	A
1/3/2024	B
1/4/2024	J
1/4/2024	G
1/4/2024	H
1/5/2024	B
1/6/2024	J
1/7/2024	H
1/8/2024	L
1/9/2024	M
1/10/2024	N

I want to calculate daily new user # - new user definition is 1) Purchased on that day and 2) No purchase in last week (from day -7 to day-1).  To do this, need to calculate "# of ID" in last week(from day -7 to day -1) by each date and ID then if this count is '0', then it will be new user.  

Order date	ID	Date -7	Date -1	# of order in last week (day -6 to day -1)	New user
12/25/2023	Q	12/18/2023	12/24/2023	n/a	n/a
12/25/2023	X	12/18/2023	12/24/2023	n/a	n/a
12/26/2023	A	12/19/2023	12/25/2023	n/a	n/a
12/27/2023	Y	12/20/2023	12/26/2023	n/a	n/a
12/28/2023	Z	12/21/2023	12/27/2023	n/a	n/a
12/29/2023	W	12/22/2023	12/28/2023	n/a	n/a
12/30/2023	P	12/23/2023	12/29/2023	n/a	n/a
12/31/2023	A	12/24/2023	12/30/2023	n/a	n/a
12/31/2023	J	12/24/2023	12/30/2023	n/a	n/a
12/31/2023	N	12/24/2023	12/30/2023	n/a	n/a
1/1/2024	A	12/25/2023	12/31/2023	3	0
1/1/2024	B	12/25/2023	12/31/2023	0	New user
1/2/2024	N	12/26/2023	1/1/2024	2
1/2/2024	D	12/26/2023	1/1/2024	0	New user
1/2/2024	F	12/26/2023	1/1/2024	0	New user
1/3/2024	A	12/27/2023	1/2/2024	3
1/3/2024	B	12/27/2023	1/2/2024	2
1/4/2024	J	12/28/2023	1/3/2024	2
1/4/2024	G	12/28/2023	1/3/2024	0	New user
1/4/2024	H	12/28/2023	1/3/2024	0	New user
1/5/2024	B	12/29/2023	1/4/2024	3
1/6/2024	J	12/30/2023	1/5/2024	3
1/7/2024	H	12/31/2023	1/6/2024	2
1/8/2024	L	1/1/2024	1/7/2024	0	New user
1/9/2024	M	1/2/2024	1/8/2024	0	New user
1/10/2024	N	1/3/2024	1/9/2024	0	New user

Final results needed is as daily new user count. 

Date	New user count
1/1/2024	1
1/2/2024	2
1/3/2024	0
1/4/2024	2
1/5/2024	0
1/6/2024	0
1/7/2024	0
1/8/2024	1
1/9/2024	1
1/10/2024	1

How can  I calculate this using Power BI?

[Dangar332]

hi, @Jinyang00 

try below code for new column 

Column = 
var a = MINX( FILTER(newcustomer,[Order date]>=EARLIER(newcustomer[Order date])-7),[Order date])
var a1 = MIN(newcustomer[Order date])+7
var b = MAXX(FILTER(newcustomer,newcustomer[Order date]<=EARLIER(newcustomer[Order date])-1),[Order date])
var c = CALCULATETABLE(VALUES(newcustomer[ID ]),newcustomer[Order date]>=a,newcustomer[Order date]<=b,REMOVEFILTERS())
return
IF(newcustomer[Order date]<a1,BLANK(),IF(newcustomer[ID ] in c ,BLANK(),1))+0

download file click HERE 

[Anonymous · Accepted solution]

Hi @Jinyang00 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create calculated columns.

Date -7 = [Order date]-7

Date -1 = [Order date]-1

# of order in last week (day -6 to day -1) = 
 var _a=CONVERT(COUNTROWS(FILTER('Table','Table'[ID]=EARLIER('Table'[ID]) && 'Table'[Order date]<=EARLIER('Table'[Date -1]) && 'Table'[Order date] >=EARLIER('Table'[Date -7]))),STRING)
 var _b=IF(_a=BLANK(),"0",_a)
 return IF([Order date]<DATE(2024,1,1),"n/a",_b)

New user = IF([# of order in last week (day -6 to day -1)]="0","new user")

(3) We can create a measure.

Count = IF(COUNT('Table'[New user])=BLANK(),0,COUNT('Table'[New user]))

(4) Then the result is as follows.

Best Regards,

Neeko Tang

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