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 : nbr activity by user and by month, nbr activity b...
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.

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])