Track the Workload by counting the number of in progress tasks

[TomLU123]

Dear Expert, 

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

Task Name	Start Date	End Date
A	11/5/2018	11/9/2018
B	11/7/2018	11/8/2018
C	11/7/2018	11/9/2018
D	11/6/2018	11/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/2018	1
11/6/2018	2
11/7/2018	4
11/8/2018	4
11/9/2018	2

Is it possible to achieve that?

Many thanks!

Best regards,

Tom

[v-lili6-msft · Accepted solution]

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

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