Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all,
I have this type of data with two columns session date and id user, I want to add to columns that :
Please fin below an example of data:
Session date | id | first use date | Session in first 30 days | Session between "first date use+150days" and "first date use+180days" |
11/01/2021 15:18 | id1 | 11/01/2021 10:11 | 4 | 2 |
11/01/2021 10:11 | id1 | 11/01/2021 10:11 | 4 | 2 |
12/01/2021 12:53 | id1 | 11/01/2021 10:11 | 4 | 2 |
09/02/2021 15:18 | id1 | 11/01/2021 10:11 | 4 | 2 |
15/03/2021 14:54 | id1 | 11/01/2021 10:11 | 4 | 2 |
15/04/2021 14:37 | id1 | 11/01/2021 10:11 | 4 | 2 |
17/05/2021 10:42 | id1 | 11/01/2021 10:11 | 4 | 2 |
01/06/2021 12:16 | id1 | 11/01/2021 10:11 | 4 | 2 |
04/07/2021 11:51 | id1 | 11/01/2021 10:11 | 4 | 2 |
26/08/2021 10:39 | id1 | 11/01/2021 10:11 | 4 | 2 |
07/01/2021 08:53 | id2 | 07/01/2021 08:53 | 2 | 3 |
06/02/2021 11:47 | id2 | 07/01/2021 08:53 | 2 | 3 |
09/03/2021 11:16 | id2 | 07/01/2021 08:53 | 2 | 3 |
09/04/2021 08:27 | id2 | 07/01/2021 08:53 | 2 | 3 |
01/05/2021 08:56 | id2 | 07/01/2021 08:53 | 2 | 3 |
31/05/2021 18:56 | id2 | 07/01/2021 08:53 | 2 | 3 |
11/06/2021 10:14 | id2 | 07/01/2021 08:53 | 2 | 3 |
02/07/2021 11:04 | id2 | 07/01/2021 08:53 | 2 | 3 |
04/07/2021 11:04 | id2 | 07/01/2021 08:53 | 2 | 3 |
09/08/2021 08:50 | id2 | 07/01/2021 08:53 | 2 | 3 |
Thank you for your help.
Best,
Hi,
You may download my PBI file from here.
Hope this helps.
Try these calculated columns. My results are slightly different, but you can tweak the pattern as necessary.
Session in first 30 days =
VAR vFirstSession = Table1[first use date]
VAR vFirstSessionPlus30 = vFirstSession + 30
VAR vResult =
CALCULATE (
COUNT ( Table1[Session date] ),
ALLEXCEPT ( Table1, Table1[id] ),
Table1[Session date] >= vFirstSession,
Table1[Session date] <= vFirstSessionPlus30
)
RETURN
vResult
Session between 150 and 180 days =
VAR vFirstSessionPlus150 = Table1[first use date] + 150
VAR vFirstSessionPlus180 = Table1[first use date] + 180
VAR vResult =
CALCULATE (
COUNT ( Table1[Session date] ),
ALLEXCEPT ( Table1, Table1[id] ),
Table1[Session date] >= vFirstSessionPlus150,
Table1[Session date] <= vFirstSessionPlus180
)
RETURN
vResult
Proud to be a Super User!
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |