Columns Calculation add

[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-henryk-mstf]

Hi @Anonymous ,

According to your needs and I did the following test, First create a date list as the judgment condition, then count the number of rows that meet the time range. Get the final result.

Column = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        [Column 2] = 1
            && 'Table'[id] = EARLIER ( 'Table'[id] )
    )
)

Nbr Activities during this period by id = 
IF ( 'Table'[Column 2] = 0, 0, MAX ( 'Table'[Column] ) )

Here is the sample pbix file.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[amitchandak · Accepted solution]

@Anonymous ,

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