Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Community I need your help!
I have the following scenario:
I have a list of tasks that are related to a sprint. In this tabele ALL tasks (active and closed) are listed. For each Task ID I have the following collumns. The collumn "Active Date" presents the day on which a task was in the state "Active". The collumn "Closed Date" represents the day on which a task was in the state "Closed/Compleded".
Now, what I'd like to do is to show in a visual the cummulative count of active and closed/completed tasks during the days of one selected Sprint. For the sprint of calendar week 43 the visual should look similar to picture 1.
I now that I need two different measures (one for the cummulative count of the tasks that are "active", and one for those that are "compleded") however the difficulty is that it should only cummulatively count the respective tasks based on the selected Sprint Week in the Filter. This means that with each start of a new sprint the measure begins to count from 0 and does not include the previous sprints into the calculation.
I've tried to make it more clear with table 2.
I hope that you guys can help me! Thank you in advance!
Here is some sample data:
Task ID | Sprint CW | Sprint Start Date | Sprint End Date | Activated Date | Completed Date |
1 | 41-42 | 08.10.2022 | 22.10.2022 | 05.10.2022 | 20.10.2022 |
2 | 41-42 | 08.10.2022 | 22.10.2022 | 06.10.2022 | 10.10.2022 |
3 | 41-42 | 08.10.2022 | 22.10.2022 | 06.10.2022 | 11.10.2022 |
4 | 41-42 | 08.10.2022 | 22.10.2022 | 06.10.2022 | 20.10.2022 |
5 | 41-42 | 08.10.2022 | 22.10.2022 | 06.10.2022 | 25.10.2022 |
6 | 41-42 | 08.10.2022 | 22.10.2022 | 07.10.2022 | 10.10.2022 |
7 | 41-42 | 08.10.2022 | 22.10.2022 | 07.10.2022 | 20.10.2022 |
8 | 41-42 | 08.10.2022 | 22.10.2022 | 12.08.2022 | 19.10.2022 |
9 | 41-42 | 08.10.2022 | 22.10.2022 | 12.08.2022 | 19.10.2022 |
10 | 41-42 | 08.10.2022 | 22.10.2022 | 12.10.2022 | 12.10.2022 |
11 | 41-42 | 08.10.2022 | 22.10.2022 | 13.10.2022 | 17.10.2022 |
12 | 41-42 | 08.10.2022 | 22.10.2022 | 14.08.2022 | null |
13 | 41-42 | 08.10.2022 | 22.10.2022 | 14.10.2022 | 21.10.2022 |
14 | 41-42 | 08.10.2022 | 22.10.2022 | 16.09.2022 | 20.10.2022 |
15 | 41-42 | 08.10.2022 | 22.10.2022 | 18.10.2022 | 18.10.2022 |
16 | 41-42 | 08.10.2022 | 22.10.2022 | 19.08.2022 | 10.10.2022 |
17 | 41-42 | 08.10.2022 | 22.10.2022 | 19.10.2022 | 20.10.2022 |
18 | 41-42 | 08.10.2022 | 22.10.2022 | 20.09.2022 | 20.10.2022 |
19 | 41-42 | 08.10.2022 | 22.10.2022 | 20.10.2022 | 21.10.2022 |
20 | 41-42 | 08.10.2022 | 22.10.2022 | 20.10.2022 | 21.10.2022 |
21 | 41-42 | 08.10.2022 | 22.10.2022 | 20.10.2022 | 21.10.2022 |
22 | 41-42 | 08.10.2022 | 22.10.2022 | 20.10.2022 | 25.10.2022 |
23 | 41-42 | 08.10.2022 | 22.10.2022 | 20.10.2022 | 21.10.2022 |
24 | 41-42 | 08.10.2022 | 22.10.2022 | 21.10.2022 | 21.10.2022 |
25 | 41-42 | 08.10.2022 | 22.10.2022 | 23.09.2022 | 20.10.2022 |
26 | 41-42 | 08.10.2022 | 22.10.2022 | 27.05.2022 | 20.10.2022 |
27 | 41-42 | 08.10.2022 | 22.10.2022 | 28.06.2022 | 20.10.2022 |
28 | 43-44 | 22.10.2022 | 05.11.2022 | 01.07.2022 | null |
29 | 43-44 | 22.10.2022 | 05.11.2022 | 03.10.2022 | 25.10.2022 |
30 | 43-44 | 22.10.2022 | 05.11.2022 | 04.10.2022 | null |
31 | 43-44 | 22.10.2022 | 05.11.2022 | 06.10.2022 | 25.10.2022 |
32 | 43-44 | 22.10.2022 | 05.11.2022 | 07.10.2022 | null |
33 | 43-44 | 22.10.2022 | 05.11.2022 | 11.08.2022 | null |
34 | 43-44 | 22.10.2022 | 05.11.2022 | 11.10.2022 | null |
35 | 43-44 | 22.10.2022 | 05.11.2022 | 11.10.2022 | null |
36 | 43-44 | 22.10.2022 | 05.11.2022 | 11.10.2022 | null |
37 | 43-44 | 22.10.2022 | 05.11.2022 | 11.10.2022 | null |
38 | 43-44 | 22.10.2022 | 05.11.2022 | 12.10.2022 | null |
39 | 43-44 | 22.10.2022 | 05.11.2022 | 12.10.2022 | null |
40 | 43-44 | 22.10.2022 | 05.11.2022 | 12.10.2022 | null |
41 | 43-44 | 22.10.2022 | 05.11.2022 | 12.10.2022 | null |
42 | 43-44 | 22.10.2022 | 05.11.2022 | 12.10.2022 | null |
43 | 43-44 | 22.10.2022 | 05.11.2022 | 12.10.2022 | null |
44 | 43-44 | 22.10.2022 | 05.11.2022 | 12.10.2022 | null |
45 | 43-44 | 22.10.2022 | 05.11.2022 | 12.10.2022 | null |
46 | 43-44 | 22.10.2022 | 05.11.2022 | 12.10.2022 | null |
47 | 43-44 | 22.10.2022 | 05.11.2022 | 12.10.2022 | null |
48 | 43-44 | 22.10.2022 | 05.11.2022 | 12.10.2022 | null |
49 | 43-44 | 22.10.2022 | 05.11.2022 | 12.10.2022 | null |
50 | 43-44 | 22.10.2022 | 05.11.2022 | 12.10.2022 | null |
51 | 43-44 | 22.10.2022 | 05.11.2022 | 12.10.2022 | null |
52 | 43-44 | 22.10.2022 | 05.11.2022 | 12.10.2022 | null |
53 | 43-44 | 22.10.2022 | 05.11.2022 | 13.10.2022 | 27.10.2022 |
54 | 43-44 | 22.10.2022 | 05.11.2022 | 14.10.2022 | null |
Solved! Go to Solution.
Hi there. I've created a sample file with the first data set (from the picture as I was too impatient to wait for your data). Please check it out. It should be what you wanted... but let me know if you've got any further questions. File attached.
Hi @Janica123
This data you've pasted... I can see it's not consistent. There are tasks without an activation date but with a closed date. There are tasks which span more than 1 sprint. There are many questions to be asked about this data. For instance, one of them would be: How should one treat tasks without an activation date? Should such data not be first cleansed in Power Query to make it... well, logical, consistent and ready for consumption by Power BI?
Hi @daXtreme ,
thank you very much for your effort!! Yes, your right I've inserted the wrong sample and all the tasks have an activation date, sorry for that. I've corrected it now in the post.
Regarding the fact that the tasks span more than one sprint, this is correct. In the use case they work with sprints with a length of 2 weeks thats why the "Sprint CW" is most often in this form. This means also, that the time span I'd like to look at are the respective dates of those two calendar weeks selected.
@daXtreme thank you very very much I think this is a very good alternatve to solve my problem! I only need to adjust the sprint week and dates to a 2week period, but I think this should not be a problem 🙂
One last question, is it also possible to cummulatively count the closed and active tasks like demonstrated in the picture in the post?
Sorry... What do you mean exactly?
I've solved it already by choosing the stackt version of the visual 🙂 Thanks again for your time and effort, I really apprechiate it!
hmm if you have a measure for count , try use :
All Time COUNT =
VAR seldate = MAX('Calendar Table'[Date])
VAR firstdate CALCULATE(FIRSTDATE([date]),REMOVEFILTERS('Calendar Table'[Date]))
Return
CALCULATE([measure],DATESBETWEEN('Calendar Table'[Date],firstdate,seldate))
this will give you aa stacking of count over each period
It would be really nice if you could supply some representative and relevant sample of your data. It's hard to retrieve text data from a picture and typing it by hand... not too much fun😩
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |