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
Janica123
Helper I
Helper I

Cummulative Count

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".

Table 1Table 1

 

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. 

picture 1picture 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. 

table 2table 2

 

I hope that you guys can help me! Thank you in advance!

 

Here is some sample data:

Task IDSprint CWSprint Start DateSprint End DateActivated DateCompleted Date
141-4208.10.202222.10.202205.10.202220.10.2022
241-4208.10.202222.10.202206.10.202210.10.2022
341-4208.10.202222.10.202206.10.202211.10.2022
441-4208.10.202222.10.202206.10.202220.10.2022
541-4208.10.202222.10.202206.10.202225.10.2022
641-4208.10.202222.10.202207.10.202210.10.2022
741-4208.10.202222.10.202207.10.202220.10.2022
841-4208.10.202222.10.202212.08.202219.10.2022
941-4208.10.202222.10.202212.08.202219.10.2022
1041-4208.10.202222.10.202212.10.202212.10.2022
1141-4208.10.202222.10.202213.10.202217.10.2022
1241-4208.10.202222.10.202214.08.2022null
1341-4208.10.202222.10.202214.10.202221.10.2022
1441-4208.10.202222.10.202216.09.202220.10.2022
1541-4208.10.202222.10.202218.10.202218.10.2022
1641-4208.10.202222.10.202219.08.202210.10.2022
1741-4208.10.202222.10.202219.10.202220.10.2022
1841-4208.10.202222.10.202220.09.202220.10.2022
1941-4208.10.202222.10.202220.10.202221.10.2022
2041-4208.10.202222.10.202220.10.202221.10.2022
2141-4208.10.202222.10.202220.10.202221.10.2022
2241-4208.10.202222.10.202220.10.202225.10.2022
2341-4208.10.202222.10.202220.10.202221.10.2022
2441-4208.10.202222.10.202221.10.202221.10.2022
2541-4208.10.202222.10.202223.09.202220.10.2022
2641-4208.10.202222.10.202227.05.202220.10.2022
2741-4208.10.202222.10.202228.06.202220.10.2022
2843-4422.10.202205.11.202201.07.2022null
2943-4422.10.202205.11.202203.10.202225.10.2022
3043-4422.10.202205.11.202204.10.2022null
3143-4422.10.202205.11.202206.10.202225.10.2022
3243-4422.10.202205.11.202207.10.2022null
3343-4422.10.202205.11.202211.08.2022null
3443-4422.10.202205.11.202211.10.2022null
3543-4422.10.202205.11.202211.10.2022null
3643-4422.10.202205.11.202211.10.2022null
3743-4422.10.202205.11.202211.10.2022null
3843-4422.10.202205.11.202212.10.2022null
3943-4422.10.202205.11.202212.10.2022null
4043-4422.10.202205.11.202212.10.2022null
4143-4422.10.202205.11.202212.10.2022null
4243-4422.10.202205.11.202212.10.2022null
4343-4422.10.202205.11.202212.10.2022null
4443-4422.10.202205.11.202212.10.2022null
4543-4422.10.202205.11.202212.10.2022null
4643-4422.10.202205.11.202212.10.2022null
4743-4422.10.202205.11.202212.10.2022null
4843-4422.10.202205.11.202212.10.2022null
4943-4422.10.202205.11.202212.10.2022null
5043-4422.10.202205.11.202212.10.2022null
5143-4422.10.202205.11.202212.10.2022null
5243-4422.10.202205.11.202212.10.2022null
5343-4422.10.202205.11.202213.10.202227.10.2022
5443-4422.10.202205.11.202214.10.2022null
1 ACCEPTED 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.

View solution in original post

9 REPLIES 9
daXtreme
Solution Sage
Solution Sage

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.  

Janica123
Helper I
Helper I

@daXtreme I've added some sample data 🙂 

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.

@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!

eliasayy
Impactful Individual
Impactful Individual

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

 

daXtreme
Solution Sage
Solution Sage

@Janica123 

 

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😩

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.

Top Solution Authors