Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 :
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,
Solved! Go to 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
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] ) )
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |