Perform calculations on unique values based on the last date

[mscabrera]

Hi!

I've been having troubles to get to the following result

I have a fact table where I have multiple projects (unique ID) but each project have multiple deliverables, in the table for example I have:

ID	StartDate	EndDate	Number of tasks
xxx-123	1/01/2023	15/01/2023	1
xxx-132	7/01/2023	7/02/2023	1
xxx-142	7/02/2023	3/03/2023	1
xxx-123	1/01/2023	1/02/2023	1
xxx-123	3/01/2023	15/01/2023	1
xxx-132	5/02/2023	8/02/2002	1

I need to count the projects delivered by month, but for example the project xxx-123 needs to count as delivered in in february, because it's the lastest date when a deliverable was made. I have a dim Date table connected to the EndDate and I've tried with this formula to get the result:
VAR _Summarized = SUMMARIZE(Table,  Table[ID], "MinStart", MIN(Table[StartDate]), MAX(Table[EndDate]))

RETURN COUNTROWS(_Summarized)

But when addind the data to a Matrix and putting months in the rows, it count the project xxx-123 both in january and february, and should only be in february that is the latest end date.

Plus I'm using the same logic and addind the StartDate in the summarized function, because I need to calculate the Average Cycle Tme of every ID (CT = EndDate - StartDate / 7) and this needs to be calculated with earliest StartDate and the Latest EndDate and that averga should affect the month in when the latest delivery was made.

For that pourpse I'm using the following dax formula:
VAR _Summarized = ADDCOLUMNS(SUMMARIZE(Table,  Table[ID], "MinStart", MIN(Table[StartDate]), MAX(Table[EndDate])), "CT", DIVIDE(Table[EndDate] -  MIN(Table[StartDate]), 7))

RETURN AVERAGEX(_Summarized, [CT])

and again, it is showing me the average cycle time for the project XXX-123 in january and also in february.

As always your help will be much appreciated

[mscabrera]

@ryan_mayu  Thank you!

[ryan_mayu]

you are welcome

[ryan_mayu]

@mscabrera 

try to create a new column 

end date2 = CALCULATE(max('Table'[EndDate]),ALLEXCEPT('Table','Table'[ID]))

 then connect the new column to your dim table. The project xxx-123 will be in the Feb

[mscabrera]

Hi @ryan_mayu , thanks for the quick reply.

I noticed that when the relation between the dim date and the fact table is deactivated, the result is the expected... When I was looking for some solution to it, I managed to create the same column that you propose, but in powerquery, but I wanted to know if there is a solution in a measure that doesn't need the addition of additional columns 

[ryan_mayu]

what's the expected output based on the sample data you provided? 

[mscabrera]

 @ryan_mayu 

This is the expected outcome:

Being Delivered the measure that count the number of unique ID delivered (last date delivered) and Cycle Time the the min DIVIDE(Max(EndaDate) - Min(StartDate), 7) 

[ryan_mayu · Accepted solution]

@mscabrera 

you can group by data in PQ and create a custom column

pls see the attachment below