Cycle time matrix calculation (distance in days between dates)

[GWafonin]

Hello! I want to create cycle time matrix. Like on the picture above.

In the beginning I had a column of attributes and a column of dates. First I transformed the data (cross join), then filtered and grouped by attributes. Of course I get absolutely different result. Any ideas? I've been trying to solve it for a long time and still didn't succeed.

My columns look like this:

PA 11/12/2021

LPLV 11/17/2021

...

[v-yangliu-msft · Accepted solution]

Hi  @GWafonin ,

I added a rand() function to act as the value.

Here are the steps you can follow：

1. Create calculated table.

Table = DISTINCT('Slice'[Milestones])

2. Create measure.

Measure =
IF(
    MAX('Table'[Milestones])=MAX('Slice'[Milestones]),0,MAX('Slice'[rand]))

3. Result:

The same will display 0, and the rest will display value.

Best Regards,

Liu Yang

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

[amitchandak]

@GWafonin ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

refer if needed

https://community.powerbi.com/t5/Desktop/Calculation-Of-Cycle-Time-Average/td-p/1392491

[GWafonin]

ID          Milestones                 Date

333	FSR	11/20/2011
333	PA	10/24/2011
333	FPE	11/29/2011
333	FPI	12/20/2011
333	LPLV	1/10/2012
333	CSR	9/27/2012
328	FSR	5/14/2012
328	PA	5/7/2012
328	FPE	5/24/2012
328	FPI	6/12/2012
328	LPLV	6/23/2012
328	DBL	8/3/2012
328	CSR	1/16/2013
410	FSR	8/30/2011
410	PA	7/25/2011
410	FPE	8/30/2011
410	FPI	9/7/2011
410	LPLV	11/4/2011
410	DBL	2/7/2012
410	CSR	5/14/2012
406	FSR	9/8/2011
406	PA	7/29/2011
406	FPE	9/20/2011
406	LPLV	1/31/2012
406	DBL	4/6/2012
406	CTMS ERM Actual	7/16/2012
406	CSR	7/16/2012
405	FSR	6/28/2011
405	PA	6/2/2011
405	FPE	6/29/2011
405	FPI	6/29/2011
405	LPLV	7/12/2011
405	CSR	9/5/2012
412	FSR	7/20/2011
412	PA	6/20/2011
412	FPE	7/21/2011
412	FPI	7/21/2011
412	LPLV	9/9/2011
412	DBL	1/30/2012
412	CSR	5/1/2012
408	FSR	9/28/2011
408	PA	4/29/2011
408	FPE	11/8/2011
408	FPI	12/1/2011
408	LPLV	12/28/2011
408	DBL	3/2/2012
408	CSR	3/21/2012
409	FSR	11/8/2011
409	PA	7/28/2011
409	FPE	11/3/2011
409	FPI	11/12/2011
409	DBL	2/3/2012
409	CSR	4/16/2012
407	FSR	5/31/2011
407	PA	5/17/2011
407	FPE	6/7/2011
407	FPI	6/19/2011
407	LPLV	8/2/2011
407	DBL	9/13/2011
407	CSR	1/20/2012
456	FSR	9/7/2010
456	PA	7/13/2010
456	FPE	8/25/2010
456	FPI	9/7/2010
456	LPLV	5/23/2011
456	DBL	10/17/2011
456	CSR	9/14/2012
498	FSR	9/23/2011
498	PA	5/16/2011
498	FPE	9/26/2011
498	FPI	10/5/2011
498	LPLV	12/26/2011
498	DBL	4/16/2012
498	CSR	8/10/2012
751	FSR	7/11/2014
751	PA	1/6/2014
751	FPE	7/28/2014
751	FPI	7/28/2014
751	LPLV	3/20/2017
751	DBL	2/28/2019
751	CSR	4/10/2019
31	FSR	11/14/2016
31	PA	10/16/2015
31	FPE	11/14/2016
31	FPI	12/7/2016
31	LPLV	9/6/2017
31	DBL	10/26/2017
31	CSR	3/16/2018
15	FSR	10/26/2012
15	PA	9/1/2011
15	FPE	11/28/2012
15	FPI	12/7/2012
15	LPLV	8/24/2013
15	DBL	7/11/2014
15	CSR	1/6/2020
22	FSR	4/9/2014
22	PA	12/17/2013
22	FPE	6/20/2014
22	FPI	6/20/2014
22	LPLV	4/10/2015
22	DBL	12/16/2015
22	CSR	2/25/2016
1040	FSR	1/20/2017
1040	PA	1/12/2017
1040	FPE	1/20/2017
1040	FPI	2/16/2017
1040	LPLV	3/14/2017
1040	DBL	5/5/2017
1040	CSR	8/8/2018
943	FSR	11/18/2015
943	PA	7/24/2015
943	FPE	5/17/2016
943	FPI	5/24/2016
598	FSR	6/16/2011
598	PA	4/26/2011
598	FPE	6/23/2011
598	FPI	7/5/2011
598	LPLV	9/6/2011
598	DBL	1/18/2012
598	CSR	8/22/2018
1064	FSR	12/1/2017
1064	PA	8/23/2017
1064	FPE	12/4/2017
1064	FPI	12/21/2017
1064	LPLV	3/25/2019
1064	DBL	5/16/2019
1064	CSR	11/21/2019
881	FSR	10/22/2019
881	PA	10/20/2017
881	FPE	3/3/2020
29	FSR	4/22/2014
29	PA	12/16/2013
29	FPE	5/2/2014
29	FPI	5/23/2014
29	LPLV	7/13/2016
29	DBL	9/21/2016
29	CSR	3/30/2017
1052	FSR	2/27/2018
1052	PA	9/8/2017
1052	FPE	3/1/2018
1052	FPI	3/14/2018
1052	LPLV	8/2/2018
1052	DBL	2/8/2019
1052	CSR	6/19/2019
1030	FSR	11/20/2016
1030	PA	9/26/2016
1030	FPE	11/20/2016
1030	FPI	11/21/2016
1030	LPLV	5/7/2017
1030	DBL	6/20/2017
1030	CSR	2/12/2018
1029	FSR	11/30/2016
1029	PA	6/17/2016
1029	FPE	2/22/2017
1029	FPI	3/1/2017
1029	LPLV	4/26/2018
1029	DBL	9/7/2018
1029	CSR	10/25/2019
11	FSR	12/19/2013
11	PA	9/13/2013
11	FPE	1/13/2014
11	FPI	1/13/2014
11	LPLV	6/12/2015
11	DBL	3/28/2016
11	CSR	8/21/2019
2	FSR	2/1/2010
2	PA	6/19/2009
2	FPE	2/3/2010
2	FPI	2/3/2010
2	LPLV	11/18/2014
2	DBL	5/1/2015
2	CSR	5/25/2017
1050	FSR	2/16/2017
1050	PA	1/14/2017
1050	FPE	2/16/2017
1050	FPI	3/6/2017
1050	LPLV	3/29/2017
1050	DBL	5/24/2017
1050	CSR	11/9/2017
545	FSR	10/22/2013
545	PA	4/30/2013
545	FPE	10/22/2013
545	FPI	11/4/2013
545	LPLV	1/18/2016
545	DBL	8/5/2016
545	CSR	9/14/2016
1041	FSR	5/25/2017
1041	PA	2/9/2017
1041	FPE	5/25/2017
1041	FPI	6/8/2017
1041	LPLV	7/10/2017
1041	DBL	8/18/2017
1041	CSR	11/14/2017
323	FSR	2/25/2012
323	FPE	2/25/2012
323	FPI	2/26/2012
323	LPLV	4/5/2012
323	CSR	9/12/2012
964	FSR	6/26/2016
964	PA	3/1/2016
964	FPE	6/26/2016
964	FPI	6/27/2016
964	LPLV	7/25/2016
964	DBL	9/1/2016
964	CSR	12/19/2016
927	FSR	8/21/2015
927	PA	7/31/2015
927	FPE	8/27/2015
927	FPI	9/12/2015
927	LPLV	10/23/2015
927	DBL	11/6/2015
927	CSR	7/15/2016
1026	FSR	9/20/2016
1026	PA	8/2/2016
1026	FPE	9/20/2016
1026	FPI	10/1/2016
1026	LPLV	11/3/2016
1026	DBL	11/28/2016
1026	CSR	9/1/2017
40	FSR	2/26/2016
40	PA	8/8/2013
40	FPE	3/1/2016
40	FPI	3/11/2016
40	LPLV	4/1/2016
40	DBL	6/24/2016
40	CSR	8/11/2016
325	FSR	3/16/2011
325	PA	2/16/2011
325	FPE	3/18/2011
325	FPI	4/12/2011
325	LPLV	10/11/2011
325	DBL	12/14/2011
325	CTMS ERM Actual	11/2/2011
325	CSR	3/20/2013
38	FSR	6/21/2012
38	PA	2/28/2012
38	FPE	6/26/2012
38	FPI	7/2/2012
38	LPLV	12/14/2012
38	DBL	2/28/2013
38	CSR	7/31/2013
324	FSR	7/18/2008
324	PA	5/15/2008
324	FPE	7/18/2008
324	FPI	8/1/2008
324	LPLV	2/14/2011
324	CSR	6/23/2011
41	FSR	7/9/2013
41	PA	4/15/2013
41	FPE	7/11/2013
41	FPI	7/11/2013
41	LPLV	8/12/2013
41	DBL	8/12/2013
41	CSR	10/16/2013
690	FSR	2/12/2016
690	PA	8/8/2013
690	FPE	2/15/2016
690	FPI	2/26/2016
690	LPLV	3/18/2016
690	DBL	6/1/2016
690	CSR	7/28/2016
596	FSR	6/27/2013
596	PA	5/8/2013
596	LPLV	8/5/2013
596	CSR	2/13/2014
789	FSR	10/2/2014
789	PA	3/10/2014
789	FPE	10/2/2014
789	FPI	11/1/2014
789	LPLV	12/17/2014
789	DBL	3/27/2015
789	CSR	5/28/2015
930	FSR	6/19/2015
930	PA	5/8/2015
930	FPE	6/19/2015
930	FPI	7/15/2015
930	LPLV	8/26/2015
930	DBL	9/11/2015
930	CSR	9/29/2015
44	FSR	2/7/2016
44	PA	8/8/2013
44	FPE	2/8/2016
44	FPI	2/12/2016
44	LPLV	3/4/2016
44	DBL	4/15/2016
44	CSR	7/28/2016
790	FSR	10/21/2014
790	PA	3/10/2014

[GWafonin]

Here's the sample Data