Date Difference with Filters

[SORPN]

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.

[BA_Pete · Accepted solution]

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:

Pete

[NikhilChenna]

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]

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.

[BA_Pete]

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

[SORPN]

Perfect Brother ! Great solution

[v-yalanwu-msft]

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:

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]

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]

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,

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!

[NikhilChenna]

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]

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]

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.