Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 DateidActivity between October 2020 and Decembre 2020Nbr Activities during this period by id 
12/02/2021id1no0
11/02/2021id1no0
02/03/2021id1no0
27/01/2021id2no0
28/01/2021id3no0
29/01/2021id4no0
30/01/2021id5no0
31/01/2021id1no0
01/02/2021id2no0
02/02/2021id6no0
03/02/2021id6no0
04/02/2021id7no0
05/02/2021id8no0
06/02/2021id9no0
07/02/2021id10no0
08/02/2021id11no0
04/12/2020id12yes1
05/12/2020id1yes6
06/12/2020id10yes2
02/11/2020id1yes6
03/11/2020id2yes3
04/11/2020id1yes6
05/11/2020id11yes1
06/11/2020id10yes2
20/10/2020id1yes6
21/10/2020id1yes6
22/10/2020id2yes3
23/10/2020id3yes1
24/10/2020id4yes1
25/10/2020id1yes6
26/10/2020id2yes3
27/10/2020id8yes1

 

Thank you for your help.

 

Best,

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , 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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , 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

Anonymous
Not applicable

Thanks @amitchandak .

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.