Bi-week Calculation

[Anonymous]

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 new 2 columns :

Activities Date	activityid	userid
08/02/2021	actid1	id1
15/02/2021	actid2	id1
17/02/2021	actid3	id1
11/02/2021	actid4	id2
23/02/2021	actid5	id3
24/02/2021	actid6	id1
25/02/2021	actid7	id1

the first column it will be the bi-week number and the second one the number of activity by user during each bi-week.

Activities Date	activityid	userid	bi-week	activity by bi-week
08/02/2021	actid1	id1	weeks 6-7	3
15/02/2021	actid2	id1	weeks 6-7	3
17/02/2021	actid3	id1	weeks 6-7	3
11/02/2021	actid4	id2	weeks 6-7	1
23/02/2021	actid5	id3	weeks 8-9	1
24/02/2021	actid6	id1	weeks 8-9	2
25/02/2021	actid7	id1	weeks 8-9	2

Thank you for your help.

Best,

[v-yuaj-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, you can create two columns as follows.

bi-week = 

var x1=WEEKNUM([Activities Date],2)-1

var x2=IF((x1/2-INT(x1/2))>0,x1-1,x1)

return

"weeks"&" "&x2&"-"&x2+1

activity by bi-week = CALCULATE(COUNTROWS('Test'),ALLEXCEPT(Test,Test[bi-week],Test[userid]))

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , I am not clear on the logic you used for the last 2 columns in second table

[Anonymous]

Hello @amitchandak, I want to claculate the total of activity maked by one users every two weeks.