Calculate columns first use date+30days

[MF_BI]

Hello all,

I have this type of data with two columns session date and id user, I want to add to columns that :

• the first column give the number of session by user between "first session date" and "first session date+30days"
• The second column : Calculate the number of session by user between "first session date+150days" and "first session date+180days".

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,

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[DataInsights]

@MF_BI,

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