Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Columns Calculation add new

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,

General Comment
Need Help
Tutorial Requests
  • v-lionel-msft's avatar
    v-lionel-msft
    5 years ago

    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.

     

1 Reply

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    5 years ago

    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.