Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculation columns

Hello everybody,

 

I need your help to make a calculate columns for my table. 

I have this kind of table, Where I want to add a 3 new columns :

  1. nbr activity by user and by month,
  2. nbr activity by user and by day,
  3. nbr activity by user and week,
activityidActivityCreatedatuser id
id14/1/21 12:00 PMid1
id24/1/21 2:30 PMid2
id34/1/21 3:00 PMid1
id47/2/21 12:00 PMid1
id58/2/21 12:00 PMid3
id69/2/21 12:00 PMid1
id710/2/21 12:00 PMid4
id811/3/21 12:00 PMid1
id912/3/21 12:00 PMid3
id1013/3/21 12:00 PMid5
id1114/3/21 12:00 PMid2
id1213/3/21 3:00 PMid5
id1316/3/21 12:00 PMid4

 

This is the result that I look for it with the 3 new columns :

 

activityidActivityCreatedatuser idWeeknumbernbr activity by user and monthnbr activity by user and by daynbr activity by user and week
id14/1/21 12:00 PMid11221
id24/1/21 2:30 PMid22111
id34/1/21 3:00 PMid13221
id47/2/21 12:00 PMid14211
id58/2/21 12:00 PMid35111
id69/2/21 12:00 PMid16211
id710/2/21 12:00 PMid47111
id811/3/21 12:00 PMid18111
id912/3/21 12:00 PMid39111
id1013/3/21 12:00 PMid510221
id1114/3/21 12:00 PMid211111
id1213/3/21 3:00 PMid512221
id1316/3/21 12:00 PMid41311

1

 

Thank you for your help.

 

Best,

 

Data Protection
Interesting Links
Tutorial Requests
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Anonymous 

    You could create below calculated columns.

    nbr activity by user and by month = 
COUNTX (
    FILTER (
        'Table',
        'Table'[user id] = EARLIER ( 'Table'[user id] )
            && MONTH ( 'Table'[ActivityCreatedat] ) = MONTH ( EARLIER ( 'Table'[ActivityCreatedat] ) )
    ),
    'Table'[activityid]
)
    nbr activity by user and by day = 
COUNTX (
    FILTER (
        'Table',
        'Table'[user id] = EARLIER ( 'Table'[user id] )
            && 'Table'[ActivityCreatedat].[Date] = EARLIER ( 'Table'[ActivityCreatedat].[Date] )
    ),
    'Table'[activityid]
)
    nbr activity by user and week = 
COUNTX (
    FILTER (
        'Table',
        'Table'[user id] = EARLIER ( 'Table'[user id] )
            && WEEKNUM ( 'Table'[ActivityCreatedat] ) = WEEKNUM ( EARLIER ( 'Table'[ActivityCreatedat] ) )
    ),
    'Table'[activityid]
)

    I'm not sure how your week number column is generated. But if you already have a week number column, you can modify the third formula like below, and the result is the same as what you show in the second table.

    nbr activity by user and week =
COUNTX (
    FILTER (
        'Table',
        'Table'[user id] = EARLIER ( 'Table'[user id] )
            && 'Table'[Weeknumber] = EARLIER ( 'Table'[Weeknumber] )
    ),
    'Table'[activityid]
)

     

    Let me know if you have any questions.

    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try new columns like

     

    new column by day =
    var _id = [user id]
    var _date = [ActivityCreatedat].date
    return
    countx(filter(Table, [user id] =_id && [ActivityCreatedat].date =_date ),[activityid])

     

     

    new column by month =
    var _id = [user id]
    var _date = eomonth([ActivityCreatedat].date ,0)
    return
    countx(filter(Table, [user id] =_id && eomonth([ActivityCreatedat].date ,0) =_date ),[activityid])

     


    new column by week =
    var _id = [user id]
    var _date = [ActivityCreatedat].date+-1*WEEKDAY([ActivityCreatedat].date,2)+1
    return
    countx(filter(Table, [user id] =_id && [ActivityCreatedat].date+-1*WEEKDAY([ActivityCreatedat].date,2)+1 =_date ),[activityid])

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    Hi Anonymous 

    You could create below calculated columns.

    nbr activity by user and by month = 
COUNTX (
    FILTER (
        'Table',
        'Table'[user id] = EARLIER ( 'Table'[user id] )
            && MONTH ( 'Table'[ActivityCreatedat] ) = MONTH ( EARLIER ( 'Table'[ActivityCreatedat] ) )
    ),
    'Table'[activityid]
)
    nbr activity by user and by day = 
COUNTX (
    FILTER (
        'Table',
        'Table'[user id] = EARLIER ( 'Table'[user id] )
            && 'Table'[ActivityCreatedat].[Date] = EARLIER ( 'Table'[ActivityCreatedat].[Date] )
    ),
    'Table'[activityid]
)
    nbr activity by user and week = 
COUNTX (
    FILTER (
        'Table',
        'Table'[user id] = EARLIER ( 'Table'[user id] )
            && WEEKNUM ( 'Table'[ActivityCreatedat] ) = WEEKNUM ( EARLIER ( 'Table'[ActivityCreatedat] ) )
    ),
    'Table'[activityid]
)

    I'm not sure how your week number column is generated. But if you already have a week number column, you can modify the third formula like below, and the result is the same as what you show in the second table.

    nbr activity by user and week =
COUNTX (
    FILTER (
        'Table',
        'Table'[user id] = EARLIER ( 'Table'[user id] )
            && 'Table'[Weeknumber] = EARLIER ( 'Table'[Weeknumber] )
    ),
    'Table'[activityid]
)

     

    Let me know if you have any questions.

    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.