Create a time series graph from a task table

[Nobdyspecial]

I'm trying to figure out how to use Power BI to create a graph of worker task encumbrance from a table so that my organization can monitor and assess our total task commitments over a period of time. We regularly send teams out on the road for various amounts of time, and need to monitor our commitments into the future.

Each task is recorded in a table with Start and End dates, and the number of workers required (among vaious other data).

Task List	Start Date	End Date	# of Pers Required
Task 1	1-Jul-24	18-Jul-24	1
Task 2	24-Jan-24	31-Jul-24	5
Task 3	1-May-24	1-Sep-24	1
Task 4	24-May-24	30-Nov-24	4
Task 5	24-May-24	30-Nov-24	8
Task 6	24-May-24	30-Nov-24	15
Task 7	16-Jun-24	31-Jan-25	9
Task 8	19-Jul-24	22-Aug-24	2
Task 9	16-Aug-24	18-Sep-24	3
Task 10	06-Jul-24	31-Jan-25	2
Task 11	08-Jul-24	31-Jan-25	2
Task 12	15-Jul-24	15-Dec-24	1
Task 13	24-Jul-24	31-Jan-25	2

I somehow need to translate this into a graph that will show total workers on any given day, such as in the red line below.

While the table above only shows 13 tasks, my spreadsheet tracks over 100 tasks up to a year in advance and uses 2 different tables that vary only in the type of work to be performed.

Any idea how I can do this?

[Anonymous · Accepted solution]

Hi @Nobdyspecial ,

In Power BI, the X and Y axes need to be definite columns of values, so I think you can create a new column.

I create a calculated column and here is the DAX code.

Column = DATEDIFF('Table'[Start Date],'Table'[End Date  ],DAY)

Then you can get what you want.

Best Regards

Yilong Zhou

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

[freginier]

Hi @Nobdyspecial  can you give us an example of what you expected and what you have using power bi ? 

[Nobdyspecial]

I'm trying to re-create that graph as we did than manually. Trying to total the # of people column for any given day given the temporary nature of the tasks and then present the data for our Director, I'm brand new to PBI. My boss recommended I try it rather than doing this manually.

[freginier]

So you need a barchart with a color for each task ? or you need to summarize the number of task for each day ? or something else ? 

[Nobdyspecial]

Need to get the total number of people employed on all tasks per day and plot that on a graph that will span over a year. The problem is how do I convert the Start/End date columns to add up the total employed on any given day.

[freginier]

You need to create a continuous value, you can't use start and end. 

So you need to create a list of date for each task try do it using power query in power bi

Then you will add this new date column on X, number of employee on Y and Task List on legend !