Given: Tasks with start date and end date; Need: Count of active tasks per day

[StillLearning1]

Hi, All,

I have a table with tasks and start/end dates (example below). My goal is to create a histogram or something that will show the count of active tasks per day, but I can't figure this one out. Any ideas?

Task	Start	Finish
Learn Power BI	1/1/22	6/1/22
Love Power BI	1/1/22	4/1/22
Hate Power BI	3/15/22	4/15/22

So in this case, for 3/30/22, the count would be 3.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-yalanwu-msft · Accepted solution]

Hi, @StillLearning1 ;

You could change it.

count_tasks = COUNTROWS(FILTER('Table1',Table1[Current_Start]<=MAX('New'[Date])&&Table1[Current_Finish]>=MIN('New'[Date])))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StillLearning1]

Perfect, thanks!

[v-yalanwu-msft]

Hi, @StillLearning1 ;

I tested it in clustered column visual and it right.

so can you share a simple file ? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StillLearning1]

I figured it out actually, just had an typo in my measure.

One more question though, when shuffling through the date hierarchy, I notice that for month, quarter, and year it only takes the last day of the time period. E.g. when it's summarized by year, I'm only getting results for tasks on 12/31. Is there anyway to get a count of active tasks for different time periods?

I updated the dashboard with more relevant data:

Given Tasks with start date and end date.pbix
Password: mUavaC7ffR
https://tutorperini.egnyte.com/dl/oBUSgsapXD

[StillLearning1]

Thanks! I feel like this is going in the right direction, however, when I try to make a chart (column in this case), it doesn't appear to be working. Any ideas? The goal is to have to a chart that shows how many tasks are active on any given day.

[v-yalanwu-msft]

Hi, @StillLearning1 ;

You could create a new table .

New = CALENDAR(MIN('Table'[Start]),MAX('Table'[Finish]))

Then create a new measure to count.

count = COUNTROWS(FILTER('Table',[Start]<=MAX('New'[Date])&&[Finish]>=MAX('New'[Date])))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.