Columns Calculation add new

[Anonymous]

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

Thank you for your help.

Best,

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Create two calculated clumns.

Activity between October 2020 and Decembre 2020 = 
VAR x = [Activities Date].[MonthNo]
VAR y = [Activities Date].[Year]
RETURN
IF(
    y = 2020 && x >= 10 && x <= 12,
    "yes", "no"
)

Nbr Activities during this period by id = 
COUNTX(
    FILTER( Sheet1, [id] = EARLIER([id]) && [Column] = "yes" ),
    [id]
) + 0

Best regards,
Lionel Chen

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