DateDiff with 2 level of id Issue

[Anonymous]

Hello,

I actually work on a project who use 2 level of granularity : ID and Revision ID. 

I just want to build a measure, usable as KPI who show the average delais for an Idea to be Close. So date difference betwen his creation date and the last modification date in the date frame who have the "C" status. 

If the ID dont have the "C" Status in the date frame he isn't in the scope of this measure. 

For the business context : 

"imagine that you have a warranty problem with your phone. You send it to the warranty department and here's what happens internally:

The issue is entered in the system with an ID.
An issue is then treated by different collaborators, each time they go through a step he gets a status.

For example.
Issue: ID1 (broken screen) 
- RevisionID 1  : Status = A (to be done)
- RevisionID 2 : Status = B (in progress)
- RevisionID 3 : Status = C (Done)

So, for BI purposes, the service manager needs to know the average delai for put an issue to "Done" status".
But cause of revisionIDs have a chronological order, if we use a date slicer, the status is relative to the revision date."

This is the data structure : 

ID	Revision id	Creation Date	Modification Date	Revision Status	Result execpted
A1	1	lundi, 1 janvier 2018	lundi, 1 janvier 2018	A	
A1	2	lundi, 1 janvier 2018	mardi, 2 janvier 2018	B	
A1	3	lundi, 1 janvier 2018	mercredi, 3 janvier 2018	A	
A1	4	lundi, 1 janvier 2018	mardi, 9 janvier 2018	C	8
A2	62	mardi, 6 février 2018	lundi, 12 février 2018	A	
A2	62	mardi, 6 février 2018	vendredi, 23 février 2018	C	17
A3	12	lundi, 12 février 2018	vendredi, 2 mars 2018	A	
A3	13	lundi, 12 février 2018	vendredi, 9 mars 2018	B	
A3	14	lundi, 12 février 2018	lundi, 12 mars 2018	C	28
A4	22	jeudi, 3 mai 2018	mardi, 12 juin 2018	A	
A4	23	jeudi, 3 mai 2018	mercredi, 13 juin 2018	C	
A4	24	jeudi, 3 mai 2018	jeudi, 14 juin 2018	B	

U can find here the sample workout with some try by myself but they actually dont work. 

Exepected Result (KPI)

Pbwx. File 

Thanks by advance

[v-lid-msft]

Hi @Anonymous,

As the Example you shared, we can create such a measure to meet your requirement.

AVG DATE DIFF FOR C =
VAR t =
    SUMMARIZECOLUMNS ( Feuil1[ID], "Diff", CALCULATE ( [DateDiff] ) )
RETURN
    SUMX ( t, [Diff] )
        / COUNTROWS ( FILTER ( t, [Diff] <> BLANK () && [Diff] <> 0 ) )

BTW, pbix as attached.

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

[Anonymous]

Hello @v-lid-msft,

Thanks for your answer

This is work fine, but when i apply a filter on ID i get this error : 

SummarizeColumns() and AddMissingItems() may not be used in this context

Any id for use filter with your formula ? 

Thanks

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can change the measure to following DAX.

AVG DATE DIFF FOR C =
VAR t =
    ADDCOLUMNS ( DISTINCT ( Feuil1[ID] ), "Diff", CALCULATE ( [DateDiff] ) )
RETURN
    SUMX ( t, [Diff] )
        / COUNTROWS ( FILTER ( t, [Diff] <> BLANK () && [Diff] <> 0 ) )

BTW, pbix as attached.

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more