Calculate the number of days between earliest and latest dates in a group

[mandini210]

I have searched the forums with *almost* success but the DAX I've found isn't helping. I have a project table in which I'm trying to calculate the length of the overal project (Project Name). In this example, the total project duration would be the datediff of 6/11/2018 - 5/4/2023, or 1788 days.  Thanks in advance for any assistance!!

LineID	Project Name	Category	Subcategory	Actual Start	Actual/Forecasted Finish	Current Duration
500	Project A	Design	ROW	6/11/2018	11/9/2018	151
501	Project A	Design	ROW	10/12/2018	10/16/2019	369
502	Project A	Design	Planning	1/31/2019	12/4/2019	307
503	Project A	Design	Environmental	11/19/2018	2/28/2020	466
504	Project A	Design	Environmental	1/23/2019	8/31/2020	586
505	Project A	Design	Environmental	1/18/2019	5/28/2020	496
506	Project A	Design	Environmental	4/24/2019	1/8/2020	259
507	Project A	Design	Environmental	2/21/2020	5/20/2020	89
508	Project A	Design	ROW	7/15/2019	12/2/2020	506
509	Project A	Design	Environmental	11/21/2019	11/16/2020	361
510	Project A	Design	Environmental	12/4/2019	11/17/2020	349
512	Project A	Design	Final Design	12/31/2019	5/18/2020	139
513	Project A	Design	Final Design	11/18/2019	6/15/2020	210
514	Project A	Design	Environmental	10/28/2019	10/7/2020	345
515	Project A	Design	Final Design	12/26/2019	9/29/2020	278
516	Project A	Design	Final Design	5/29/2020	9/16/2020	110
517	Project A	Design	Final Design	9/17/2020	1/8/2021	113
518	Project A	Design	Procurement	1/8/2021	5/6/2021	118
519	Project A	Construction	Construction	5/6/2021	5/4/2023	728

[lbendlin]

There are many ways that can be done. One would be via SUMMARIZE(), another one via ALLEXCEPT() etc.  What have you tried so far?

[mandini210]

I've tried the calculate (https://community.powerbi.com/t5/Desktop/getting-the-max-and-min-date-of-a-group/td-p/680844); i've tried the allexcept (https://community.powerbi.com/t5/Desktop/Help-With-finding-the-earliest-Date/td-p/146353); i've tried https://community.powerbi.com/t5/Desktop/Using-earliest-and-latest-dates/td-p/574672. 

I've tried so many they've all blurred together! I feel like maybe I'm conflating a few different ones at this point.

[lbendlin]

Here's a very simple example. Put your Project Name in a table visual and then add this measure

Max Duration = CALCULATE(datediff(min('Duration'[Actual Start]),max('Duration'[Actual/Forecasted Finish]),DAY))

(I added a Project B with a different end date to show the difference)

[mandini210]

Your output is exactly what I want! When I used your code with my table name, I get "The expression specified in the query is not a valid table expression."

Can you explain what you mean by putting my project name into a table visual?

Thank you so much for your help!

[lbendlin]

"When I used your code with my table name, I get "The expression specified in the query is not a valid table expression.""

Please elaborate. I don't understand what you are doing.

In your pbix file go to the UI section, place a table visual onto the canvas, add the "Project Name" column to the visual.

[mandini210]

Am I understanding you to say that I have to have two tables? My original table that I pasted a section of in my original post, and another table of project names?

[lbendlin · Accepted solution]

there is a difference between a table, and a table visual.  The former has your data, the latter is a Power BI User interface element.

On the right is a table. On the left are visuals, and the highlighted one is a table visual.

[mandini210]

I have never been so thankful for smart computer people in my LIFE!! You are amazing and moved to the top of the "gold star for being awesome" list.

My disconnect was thinking it was a column, not a measure. This works perfectly!