cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TomLU123
Helper III
Helper III

Track the Workload by counting the number of in progress tasks

Dear Expert, 

 

I have a data set as below which stores the task information.

Task NameStart DateEnd Date
A11/5/201811/9/2018
B11/7/201811/8/2018
C11/7/201811/9/2018
D11/6/201811/8/2018

 

I wish to create a table to track the workload of each date. The workload is based on the number of task in progress (start but not end). By using the tasks above, following table is the expected output:

11/5/20181
11/6/20182
11/7/20184
11/8/20184
11/9/20182

 

Is it possible to achieve that?

Many thanks!

 

Best regards,

Tom

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi, @TomLU123

After my test, you could do these as below:

Step1:

Create a complete calendar table

Step2:

Use crossjoin create a new table

 

Table = FILTER(CROSSJOIN(Table1,'Date'),'Date'[Date]>=Table1[Start Date]&&'Date'[Date]<=Table1[End Date])

Step3:

 

then drag the date field form new table and count Task Name

4.JPG

 

here is my pbix, please try it.

https://www.dropbox.com/s/cnzpnm02wz5nx93/Track%20the%20Workload%20by%20counting%20the%20number%20of...

 

 

There's another way for you to refer to

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

 

Best Regards,

Lin

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

View solution in original post

1 REPLY 1
v-lili6-msft
Community Support
Community Support

hi, @TomLU123

After my test, you could do these as below:

Step1:

Create a complete calendar table

Step2:

Use crossjoin create a new table

 

Table = FILTER(CROSSJOIN(Table1,'Date'),'Date'[Date]>=Table1[Start Date]&&'Date'[Date]<=Table1[End Date])

Step3:

 

then drag the date field form new table and count Task Name

4.JPG

 

here is my pbix, please try it.

https://www.dropbox.com/s/cnzpnm02wz5nx93/Track%20the%20Workload%20by%20counting%20the%20number%20of...

 

 

There's another way for you to refer to

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

 

Best Regards,

Lin

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

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!