Duration between two dates on multiple levels

[venjktry]

Hi,

I have a list of tasks for project management. Each oftask is categorised under several subprojects, which in turn are grouped under multiple projects. I have a 'Start Date' and 'End Date' for each task. I have slicers for projects, subprojects and tasks. Calculating the duration at the task level is no brainer, but I'm clueless about calculating the same at the subproject and project (all run concurrently) level. Here's a sample of my data:

Task	Subproject	Project	Start date	End date
X	XXR	IT	1-Oct-18	30-Nov-18
Y	SQE	IT	1-Jul-18	31-Jul-18
C	DDF	Operations	13-Jul-18	2-Aug-18
Z	DFG	Legal	3-Aug-18	23-Aug-18
P	SWW	Legal	5-Nov-18	10-Dec-18
F	SWS	HR	5-Nov-18	22-Feb-19

Regards

Reply

POWER PLATFORM

[#2]

Hi,

I have a list of tasks for project management. Each oftask is categorised under several subprojects, which in turn are grouped under multiple projects. I have a 'Start Date' and 'End Date' for each task. I have slicers for projects, subprojects and tasks. Calculating the duration at the task level is no brainer, but I'm clueless about calculating the same at the subproject and project (all run concurrently) level. Here's a sample of my data:

Task	Subproject	Project	Start date	End date
X	XXR	IT	1-Oct-18	30-Nov-18
Y	SQE	IT	1-Jul-18	31-Jul-18
C	DDF	Operations	13-Jul-18	2-Aug-18
Z	DFG	Legal	3-Aug-18	23-Aug-18
P	SWW	Legal	5-Nov-18	10-Dec-18
F	SWS	HR	5-Nov-18	22-Feb-19

Regards

[JarroVGIT]

Create the following measures:

mStartDate = MIN(Table1[Start Date])

mEndDate = MAX(Table1[End Date])

Then create a Matrix visual with rows (in order): project, subproject, task and as values the two measures. THe results are like this (I added an extra task under project IT, subproject XXR task A for clarity:

This does assume that a subproject can only belong to one projects and a task can only belong to one subproject.

Let me know if this works for you!

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[venjktry]

@JarroVGIT Thanks for the reply. I guess I should've been a bit clearer. I have two slicers (one each for 'Projects' and 'Subprojects') and, preferably, a search box for 'Tasks'. When I select any of the values, the corresponding duration should show up on the report (say via a label).

Regards,

venjktry

[JarroVGIT · Accepted solution]

You can create a measure like this:

Duration = DATEDIFF(mStartDate, mEndDate, DAY)

and show that in a card in the report. Then, based on the slicers, it will automatically calculate the duration based on your current selection.

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[venjktry]

Sigh, I started off with the same formula but used it as a calculated column rather than as a measure. Thank you very much!

Regards,

venjktry