cancel
Showing results for
Did you mean:
Helper III

Hello everybody,

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

I have this kind of table, Where I want to add a new column "Activity between October 2020 and Decembre 2020" that tel us if the person make activity during this period or not.

and the second column calculate "Nbr Activities during this period by id"

As the example :

• for id1, he have make 6 activities during the period
• for id2, he have make 3 activities during the period
• for id8, he have make only 1 activity during the period
• etc...

 Activities Date id Activity between October 2020 and Decembre 2020 Nbr Activities during this period by id 12/02/2021 id1 no 0 11/02/2021 id1 no 0 02/03/2021 id1 no 0 27/01/2021 id2 no 0 28/01/2021 id3 no 0 29/01/2021 id4 no 0 30/01/2021 id5 no 0 31/01/2021 id1 no 0 01/02/2021 id2 no 0 02/02/2021 id6 no 0 03/02/2021 id6 no 0 04/02/2021 id7 no 0 05/02/2021 id8 no 0 06/02/2021 id9 no 0 07/02/2021 id10 no 0 08/02/2021 id11 no 0 04/12/2020 id12 yes 1 05/12/2020 id1 yes 6 06/12/2020 id10 yes 2 02/11/2020 id1 yes 6 03/11/2020 id2 yes 3 04/11/2020 id1 yes 6 05/11/2020 id11 yes 1 06/11/2020 id10 yes 2 20/10/2020 id1 yes 6 21/10/2020 id1 yes 6 22/10/2020 id2 yes 3 23/10/2020 id3 yes 1 24/10/2020 id4 yes 1 25/10/2020 id1 yes 6 26/10/2020 id2 yes 3 27/10/2020 id8 yes 1

Best,

1 ACCEPTED SOLUTION
Super User IV

@MFATNASSI , Try new columns liek

Activity between October 2020 and Decembre 2020 = if(isblank(countx(filter(Table, Table[ID] = earlier([ID) && [Date] >=date(2020,10,1) && [Date] <=date(2020,12,31)),[ID])), "No", "Yes")

Nbr Activities during this period by id = countx(filter(Table, Table[ID] = earlier([ID) && [Date] >=date(2020,10,1) && [Date] <=date(2020,12,31)),[ID])+0

Proud to be a Super User!

2 REPLIES 2
Super User IV

@MFATNASSI , Try new columns liek

Activity between October 2020 and Decembre 2020 = if(isblank(countx(filter(Table, Table[ID] = earlier([ID) && [Date] >=date(2020,10,1) && [Date] <=date(2020,12,31)),[ID])), "No", "Yes")

Nbr Activities during this period by id = countx(filter(Table, Table[ID] = earlier([ID) && [Date] >=date(2020,10,1) && [Date] <=date(2020,12,31)),[ID])+0

Proud to be a Super User!

Helper III

Thanks @amitchandak .

Announcements