Want to create a table which include date wise unique count of users who are completely fresh

[ddpl]

I have table which contains User_ID and Date Column from 1 st of Jan 2022 represent login activity on website

I Want to create a table which include date wise unique count of users who are completely fresh (never visited before)
Like on 2nd January there are 10 new user who are not visited in 1st of January or on 1st of March there are 7 new user who are not visited in last two month(Jan and Feb)

Please give me best and easy way to do it.

Thanks in advance

[ddpl · Accepted solution]

@speedramps Thanks for your time

But we required to create a line chart as per daily fresh inflow of user.Please find attached snap wrt required line chart with automatic uopdate the same unique count day wise.

Table1:

User ID	Date
A	1/1/2022
B	1/1/2022
C	1/1/2022
D	1/1/2022
B	1/2/2022
D	1/2/2022
E	1/2/2022
D	1/3/2022
F	1/3/2022
G	1/3/2022

Output:

Date	Unique Count of user
1/1/2022	4
1/2/2022	1
1/3/2022	2

[v-yangliu-msft · Accepted solution]

Hi  @ddpl ,

Here are the steps you can follow：

1. In Power query. Add Column – Index Column – From 1.

2. Create measure.

Flag =
var _1=CALCULATE(MAX('Table'[User ID]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])&&'Table'[Date]<=MAX('Table'[Date])))
var _2=CALCULATE(MAX('Table'[User ID]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])&&'Table'[Date]=MAX('Table'[Date])))
return
CALCULATE(DISTINCTCOUNT('Table'[User ID]), FILTER(ALL('Table'), _2 in SELECTCOLUMNS('Table',"id",_1)&&'Table'[Date]<=MAX('Table'[Date])))

Unique Count of user =
var _index1=MAXX(FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])),[Flag])
var _index2=MAXX(FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])-1),[Flag])
return
_index1 - _index2

2. Result:

Best Regards,

Liu Yang

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

[ddpl · Accepted solution]

@v-yangliu-msft 

Its worked, Thanks a lot for your time

[v-yangliu-msft · Accepted solution]

Hi  @ddpl ,

Here are the steps you can follow：

1. In Power query. Add Column – Index Column – From 1.

2. Create measure.

Flag =
var _1=CALCULATE(MAX('Table'[User ID]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])&&'Table'[Date]<=MAX('Table'[Date])))
var _2=CALCULATE(MAX('Table'[User ID]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])&&'Table'[Date]=MAX('Table'[Date])))
return
CALCULATE(DISTINCTCOUNT('Table'[User ID]), FILTER(ALL('Table'), _2 in SELECTCOLUMNS('Table',"id",_1)&&'Table'[Date]<=MAX('Table'[Date])))

Unique Count of user =
var _index1=MAXX(FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])),[Flag])
var _index2=MAXX(FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])-1),[Flag])
return
_index1 - _index2

2. Result:

Best Regards,

Liu Yang

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

[ddpl · Accepted solution]

Hi Liu

Can I use below dax instead of yours?

Flag New = CALCULATE(DISTINCTCOUNT('Table'[User ID]), FILTER(ALL('Table'),'Table'[DDate]<=MAX('Table'[Date])))

Unique Count of USer  =

var _index1=MAXX(FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])),[Flag New])

var _index2=MAXX(FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])-1),[Flag New])

return

_index1 - _index2

This also give me same result.

[ddpl · Accepted solution]

@v-yangliu-msft 

Its worked, Thanks a lot for your time

[ddpl · Accepted solution]

@speedramps Thanks for your time

But we required to create a line chart as per daily fresh inflow of user.Please find attached snap wrt required line chart with automatic uopdate the same unique count day wise.

Table1:

User ID	Date
A	1/1/2022
B	1/1/2022
C	1/1/2022
D	1/1/2022
B	1/2/2022
D	1/2/2022
E	1/2/2022
D	1/3/2022
F	1/3/2022
G	1/3/2022

Output:

Date	Unique Count of user
1/1/2022	4
1/2/2022	1
1/3/2022	2

[speedramps]

Try this and please click the thumbs up for me helping you.
And click Accept As Solution if it fixes your problem 

create this measure

New Users =

VAR myset = VALUES(Facts[User ID] )

RETURN

"(" &

CONCATENATEX (

myset ,

Facts[User ID],

", " )

& ")"

then drag and drop Date and New Users to a table visual

[selimovd]

Hey @ddpl ,

can you provide an example file?

That would make it easier to provide a solution that works for you.

Best regards

Denis

[ddpl]

I have data...

User ID	Date
A	01-01-22
B	01-01-22
C	01-01-22
D	01-01-22
B	01-02-22
D	01-02-22
E	01-02-22
D	01-03-22
F	01-03-22
G	01-03-22

I want output as below...

Fresh Users	Date
4	01-01-22	(A,B,C,D)
1	01-02-22	(E)
2	01-03-22	(F,G)

[speedramps]

Consider this solution,

Please click the thumbs up for me helping you.
And click Accept As Solution if it fixes your problem 

Number of new customers =
/* Documentation
Get number of new customers as follows:-
Use addcolumns to get subset with (CustomerKey,Previous Rows)
Then filter the subset to just to include CustomerKeys with no previous rows.
Then count the number of Customers
*/

VAR mindate = MIN ( 'Calendar'[Date] )

VAR NewCustomers =
FILTER (
ADDCOLUMNS (
VALUES ( FactSales[CustomerKey] ),
"PreviousRows",
CALCULATE (COUNTROWS (FactSales ),
FILTER (ALL ( 'Calendar'[Date] ),'Calendar'[Date] < mindate))
),
[PreviousRows] = 0
)

RETURN
COUNTROWS(NewCustomers)