Forum Discussion
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.
Hi Anonymous ,
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:
Pete
11 Replies
Hi Anonymous ,
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:
Pete
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!Hi Anonymous ,
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
Hi, Anonymous ;
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.Hi Anonymous ,
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,
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.Regards,
Nikhil Chenna
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!Hi Anonymous , 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!
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,
Hi, Anonymous ;
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:
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.
