Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MF_BI
Regular Visitor

Calculate columns first use date+30days

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 idfirst use date Session in first 30 daysSession between "first date use+150days" and "first date use+180days"
11/01/2021 15:18id111/01/2021 10:1142
11/01/2021 10:11id111/01/2021 10:1142
12/01/2021 12:53id111/01/2021 10:1142
09/02/2021 15:18id111/01/2021 10:1142
15/03/2021 14:54id111/01/2021 10:1142
15/04/2021 14:37id111/01/2021 10:1142
17/05/2021 10:42id111/01/2021 10:1142
01/06/2021 12:16id111/01/2021 10:1142
04/07/2021 11:51id111/01/2021 10:1142
26/08/2021 10:39id111/01/2021 10:1142
07/01/2021 08:53id207/01/2021 08:5323
06/02/2021 11:47id207/01/2021 08:5323
09/03/2021 11:16id207/01/2021 08:5323
09/04/2021 08:27id207/01/2021 08:5323
01/05/2021 08:56id207/01/2021 08:5323
31/05/2021 18:56id207/01/2021 08:5323
11/06/2021 10:14id207/01/2021 08:5323
02/07/2021 11:04id207/01/2021 08:5323
04/07/2021 11:04id207/01/2021 08:5323
09/08/2021 08:50id207/01/2021 08:5323

Thank you for your help.

 

Best,

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
DataInsights
Super User
Super User

@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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.