Filtering Data

[Anonymous]

Hi

This is my current data: 

Client	Service	Start Date	End Date
120	A	01/04/2014	20/02/2017
125	B	01/04/2014	13/06/2014
125	B	22/10/2021
126	C	01/04/2014	20/07/2016
127	D	01/04/2014
128	E	01/04/2014
129	F	19/04/2021	31/05/2021
130	G	01/04/2014	31/03/2021
130	G	01/04/2021
132	I	01/04/2014
133	J	01/04/2014	26/03/2017
134	K	01/04/2014
135	L	01/04/2014	25/03/2019
135	L	26/03/2019
136	M	01/04/2014	22/11/2016
136	M	23/11/2016	19/12/2016
136	M	20/12/2016	07/03/2017

I needed the outcome to be as the below table to calculate the Average number of dates a service took to close:

Average Number of dates by Unique client ID

Client	Service	Start Date	End Date	DATEDIF
120	A	01/04/2014	20/02/2017	1056
126	C	01/04/2014	20/07/2016	841
129	F	19/04/2021	31/05/2021	42
133	J	01/04/2014	26/03/2017	1090
136	M	01/04/2014	22/11/2016	966
136	M	23/11/2016	19/12/2016	26
136	M	20/12/2016	07/03/2017	77

thanks

[v-jianboli-msft · Accepted solution]

Hi @Anonymous 

Please try:

First create a measure to filter the table:

Flag =

var _t = SUMMARIZE(FILTER(ALL('Table'),[Service]=MAX('Table'[Service])),'Table'[End Date])

RETURN IF(BLANK()in _t,0,1)

Then apply it to the filter:

Create datediff:

datediff = DATEDIFF(MAX('Table'[Start Date]),MAX('Table'[End Date]),DAY)

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Sorry for the late reply but I just got back into this and it did work!

thank you ver much!

[amitchandak]

@Anonymous ,

Try a measure

measure =

var _1 = countx(filter(allselected(Table) , Table[ID] = max(Table[ID]) && isblank(Table[end date])), Table[ID] )
return
Averagex(filter(values(Table[ID), isblank(_1) ), datediff(Min(Table[Start Date]), Max(Table[End Date]), Day) )