cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SORPN
Frequent Visitor

Date Difference with Filters

Project No.

Tasks in the Projects

Start Date

End Date

AB10001

XXYYZZ12

8/24/2022

9/5/2022

AB10001

XXYYZZ13

8/25/2022

9/6/2022

AB10001

XXYYZZ14

8/26/2022

9/6/2022

AB10001

XXYYZZ15

8/26/2022

9/6/2022

AB10001

XXYYZZ16

8/26/2022

9/7/2022

AB10002

XXYYZZ12

7/24/2022

9/5/2022

AB10002

XXYYZZ13

7/25/2022

9/6/2022

AB10002

XXYYZZ14

8/26/2022

10/6/2022

AB10002

XXYYZZ15

8/26/2022

9/6/2022

AB10002

XXYYZZ16

8/26/2022

9/7/2022

AB10003

XXYYZZ12

7/24/2022

9/5/2022

AB10003

XXYYZZ13

7/25/2022

9/6/2022

AB10003

XXYYZZ14

9/26/2022

10/6/2022

AB10003

XXYYZZ15

8/26/2022

11/6/2022

AB10003

XXYYZZ16

8/26/2022

11/12/2022

I have 3 different project and there are multiple actions hapeening in each project, the Lead time for project is diff. b/w latest End date and Earliest start date of that project. I need DAX to find Lead time for the projects and Total Average Lead time for the 3 Projects combined., after loading from Query environment. If not possible suggest me an alternative.

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @SORPN ,

 

You just need one measure for all of your requirements:

_projectLeadTime = 
AVERAGEX(
    SUMMARIZE(
        yourTable,
        yourTable[Project No.],
        "minStart", MIN(yourTable[Start Date]),
        "maxEnd", MAX(yourTable[End Date])
    ),
    DATEDIFF([minStart], [maxEnd], DAY)
)

 

Here's the output when applied against different levels of dimensions:

BA_Pete_0-1669367862417.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

11 REPLIES 11
BA_Pete
Super User
Super User

Hi @SORPN ,

 

You just need one measure for all of your requirements:

_projectLeadTime = 
AVERAGEX(
    SUMMARIZE(
        yourTable,
        yourTable[Project No.],
        "minStart", MIN(yourTable[Start Date]),
        "maxEnd", MAX(yourTable[End Date])
    ),
    DATEDIFF([minStart], [maxEnd], DAY)
)

 

Here's the output when applied against different levels of dimensions:

BA_Pete_0-1669367862417.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




NikhilChenna
Continued Contributor
Continued Contributor

Hi @BA_Pete ,

Great optimised solution brother. Averagex did not came to my mind only.

 

Regards,

Nikhil Chenna

 

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

SORPN
Frequent Visitor

A your previous suggestion was good enough, I'd like to ask you to suggest a method to find out average of corresponding task in the projects, Since a project (for eg : xxyyzz12 - exist in every project what would be the collective average in all projects combined.

Hi @SORPN ,

 

You can adjust the scope of the measure but changing which columns are included within the SUMMARIZE function.

You could swap 'yourTable[Project No.]'  for 'yourTable[Tasks in the Projects]' to focus the measure just on the tasks, or you could add this field into the SUMMARIZE function and keep 'yourTable[Project No.]' as well, which would probably do what you want it to.

Try this measure and see if it does what you want:

_projectTaskLeadTimes = 
AVERAGEX(
    SUMMARIZE(
        yourTable,
        yourTable[Project No.],
        yourTable[Tasks in the Projects],
        "minStart", MIN(yourTable[Start Date]),
        "maxEnd", MAX(yourTable[End Date])
    ),
    DATEDIFF([minStart], [maxEnd], DAY)
)

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




SORPN
Frequent Visitor

Perfect Brother ! Great solution

v-yalanwu-msft
Community Support
Community Support

Hi, @SORPN ;

You could create a measure by dax.

Measure = DIVIDE(
SUMX(SELECTCOLUMNS('Table',"Project",[Project Nd],"1",
CALCULATE(DATEDIFF(MIN('Table'[Start Date ]), MAX('Table'[End Date]),DAY),ALLEXCEPT('Table','Table'[Project Nd]))),[1]),
COUNTROWS('Table'))

The final show:

vyalanwumsft_1-1669361141084.png


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

SORPN
Frequent Visitor

Project No.

Task Lead Time

Project Lead Time

AB10001

12

14

AB10001

12

14

AB10001

11

14

AB10001

11

14

AB10001

12

14

AB10002

43

74

AB10002

43

74

AB10002

41

74

AB10002

11

74

AB10002

12

74

AB10003

43

111

AB10003

43

111

AB10003

10

111

AB10003

72

111

AB10003

78

111

NikhilChenna
Continued Contributor
Continued Contributor

Hi @SORPN ,

 

I think you can simply do a summarize table of the above table i tried it my self with the below code, you have to replace the table name , 

 

For creating a table, you have to go to the modelling tab and click on the new table as shown below,

NikhilChenna_1-1669359465855.png

 

 

Summarized_table5 =
SUMMARIZE(
'Table (5)',
'Table (5)'[Project No.],
"Max Start date",MAX('Table (5)'[Start Date]),
"Max End date",MAX('Table (5)'[End Date] ))
 
After this you can simply add a calculated column , Lead time - 
Lead time = Summarized_table5[Max End date]-Summarized_table5[Max Start date]
 
You' ll be able to  see individual how much time did they took project wise and if you total all three lead time values you'll get the combined number for it. 
 
NikhilChenna_0-1669359317725.png

 

Regards,

Nikhil Chenna

 

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

Hi @SORPN , If this solves your issue.

 

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

SORPN
Frequent Visitor

So as far as task is concern, the lead time is simple, start date - end date difference. But when it comes to project. I need the Difference between The date of latest end task & date of earliest started task. And finally after this, I need to view the total average of 3 Project,  Not as Line 1+2+3...+15 and divide by 15, 

v-yalanwu-msft
Community Support
Community Support

Hi, @SORPN ;

First of all, for Dax formulas to be loaded into the Power Query environment, this will not work, the custom columns of Power Query can be loaded into Power BI Desktop, and vice versa, this is by design. Secondly, what is the calculation logic for the lead time in your sample? Or you can share the display of the results you want to output to make it easier to understand the logic behind it.
Looking forward to your reply!


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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors