Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
venjktry
Regular Visitor

Duration between two dates on multiple levels

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:

 

TaskSubprojectProjectStart dateEnd date
XXXRIT1-Oct-1830-Nov-18
YSQEIT1-Jul-1831-Jul-18
CDDFOperations13-Jul-182-Aug-18
ZDFGLegal3-Aug-1823-Aug-18
PSWWLegal5-Nov-1810-Dec-18
FSWSHR5-Nov-1822-Feb-19

 

Regards

 
 
 
 
 
 
 

POWER PLATFORM

1 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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
JarroVGIT
Resident Rockstar
Resident Rockstar

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:

image.png

 

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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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

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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors