Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
GWafonin
Advocate III
Advocate III

Cycle time matrix calculation (distance in days between dates)

GWafonin_0-1646728653692.png

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

GWafonin_1-1646728698135.png

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

...

 

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @GWafonin ,

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

vyangliumsft_0-1647407085135.png

Here are the steps you can follow:

1. Create calculated table.

Table = DISTINCT('Slice'[Milestones])

vyangliumsft_1-1647407085136.png

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.

vyangliumsft_2-1647407085140.png

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

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @GWafonin ,

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

vyangliumsft_0-1647407085135.png

Here are the steps you can follow:

1. Create calculated table.

Table = DISTINCT('Slice'[Milestones])

vyangliumsft_1-1647407085136.png

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.

vyangliumsft_2-1647407085140.png

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
Super User
Super User

@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

ID          Milestones                 Date

333FSR11/20/2011
333PA10/24/2011
333FPE11/29/2011
333FPI12/20/2011
333LPLV1/10/2012
333CSR9/27/2012
328FSR5/14/2012
328PA5/7/2012
328FPE5/24/2012
328FPI6/12/2012
328LPLV6/23/2012
328DBL8/3/2012
328CSR1/16/2013
410FSR8/30/2011
410PA7/25/2011
410FPE8/30/2011
410FPI9/7/2011
410LPLV11/4/2011
410DBL2/7/2012
410CSR5/14/2012
406FSR9/8/2011
406PA7/29/2011
406FPE9/20/2011
406LPLV1/31/2012
406DBL4/6/2012
406CTMS ERM Actual7/16/2012
406CSR7/16/2012
405FSR6/28/2011
405PA6/2/2011
405FPE6/29/2011
405FPI6/29/2011
405LPLV7/12/2011
405CSR9/5/2012
412FSR7/20/2011
412PA6/20/2011
412FPE7/21/2011
412FPI7/21/2011
412LPLV9/9/2011
412DBL1/30/2012
412CSR5/1/2012
408FSR9/28/2011
408PA4/29/2011
408FPE11/8/2011
408FPI12/1/2011
408LPLV12/28/2011
408DBL3/2/2012
408CSR3/21/2012
409FSR11/8/2011
409PA7/28/2011
409FPE11/3/2011
409FPI11/12/2011
409DBL2/3/2012
409CSR4/16/2012
407FSR5/31/2011
407PA5/17/2011
407FPE6/7/2011
407FPI6/19/2011
407LPLV8/2/2011
407DBL9/13/2011
407CSR1/20/2012
456FSR9/7/2010
456PA7/13/2010
456FPE8/25/2010
456FPI9/7/2010
456LPLV5/23/2011
456DBL10/17/2011
456CSR9/14/2012
498FSR9/23/2011
498PA5/16/2011
498FPE9/26/2011
498FPI10/5/2011
498LPLV12/26/2011
498DBL4/16/2012
498CSR8/10/2012
751FSR7/11/2014
751PA1/6/2014
751FPE7/28/2014
751FPI7/28/2014
751LPLV3/20/2017
751DBL2/28/2019
751CSR4/10/2019
31FSR11/14/2016
31PA10/16/2015
31FPE11/14/2016
31FPI12/7/2016
31LPLV9/6/2017
31DBL10/26/2017
31CSR3/16/2018
15FSR10/26/2012
15PA9/1/2011
15FPE11/28/2012
15FPI12/7/2012
15LPLV8/24/2013
15DBL7/11/2014
15CSR1/6/2020
22FSR4/9/2014
22PA12/17/2013
22FPE6/20/2014
22FPI6/20/2014
22LPLV4/10/2015
22DBL12/16/2015
22CSR2/25/2016
1040FSR1/20/2017
1040PA1/12/2017
1040FPE1/20/2017
1040FPI2/16/2017
1040LPLV3/14/2017
1040DBL5/5/2017
1040CSR8/8/2018
943FSR11/18/2015
943PA7/24/2015
943FPE5/17/2016
943FPI5/24/2016
598FSR6/16/2011
598PA4/26/2011
598FPE6/23/2011
598FPI7/5/2011
598LPLV9/6/2011
598DBL1/18/2012
598CSR8/22/2018
1064FSR12/1/2017
1064PA8/23/2017
1064FPE12/4/2017
1064FPI12/21/2017
1064LPLV3/25/2019
1064DBL5/16/2019
1064CSR11/21/2019
881FSR10/22/2019
881PA10/20/2017
881FPE3/3/2020
29FSR4/22/2014
29PA12/16/2013
29FPE5/2/2014
29FPI5/23/2014
29LPLV7/13/2016
29DBL9/21/2016
29CSR3/30/2017
1052FSR2/27/2018
1052PA9/8/2017
1052FPE3/1/2018
1052FPI3/14/2018
1052LPLV8/2/2018
1052DBL2/8/2019
1052CSR6/19/2019
1030FSR11/20/2016
1030PA9/26/2016
1030FPE11/20/2016
1030FPI11/21/2016
1030LPLV5/7/2017
1030DBL6/20/2017
1030CSR2/12/2018
1029FSR11/30/2016
1029PA6/17/2016
1029FPE2/22/2017
1029FPI3/1/2017
1029LPLV4/26/2018
1029DBL9/7/2018
1029CSR10/25/2019
11FSR12/19/2013
11PA9/13/2013
11FPE1/13/2014
11FPI1/13/2014
11LPLV6/12/2015
11DBL3/28/2016
11CSR8/21/2019
2FSR2/1/2010
2PA6/19/2009
2FPE2/3/2010
2FPI2/3/2010
2LPLV11/18/2014
2DBL5/1/2015
2CSR5/25/2017
1050FSR2/16/2017
1050PA1/14/2017
1050FPE2/16/2017
1050FPI3/6/2017
1050LPLV3/29/2017
1050DBL5/24/2017
1050CSR11/9/2017
545FSR10/22/2013
545PA4/30/2013
545FPE10/22/2013
545FPI11/4/2013
545LPLV1/18/2016
545DBL8/5/2016
545CSR9/14/2016
1041FSR5/25/2017
1041PA2/9/2017
1041FPE5/25/2017
1041FPI6/8/2017
1041LPLV7/10/2017
1041DBL8/18/2017
1041CSR11/14/2017
323FSR2/25/2012
323FPE2/25/2012
323FPI2/26/2012
323LPLV4/5/2012
323CSR9/12/2012
964FSR6/26/2016
964PA3/1/2016
964FPE6/26/2016
964FPI6/27/2016
964LPLV7/25/2016
964DBL9/1/2016
964CSR12/19/2016
927FSR8/21/2015
927PA7/31/2015
927FPE8/27/2015
927FPI9/12/2015
927LPLV10/23/2015
927DBL11/6/2015
927CSR7/15/2016
1026FSR9/20/2016
1026PA8/2/2016
1026FPE9/20/2016
1026FPI10/1/2016
1026LPLV11/3/2016
1026DBL11/28/2016
1026CSR9/1/2017
40FSR2/26/2016
40PA8/8/2013
40FPE3/1/2016
40FPI3/11/2016
40LPLV4/1/2016
40DBL6/24/2016
40CSR8/11/2016
325FSR3/16/2011
325PA2/16/2011
325FPE3/18/2011
325FPI4/12/2011
325LPLV10/11/2011
325DBL12/14/2011
325CTMS ERM Actual11/2/2011
325CSR3/20/2013
38FSR6/21/2012
38PA2/28/2012
38FPE6/26/2012
38FPI7/2/2012
38LPLV12/14/2012
38DBL2/28/2013
38CSR7/31/2013
324FSR7/18/2008
324PA5/15/2008
324FPE7/18/2008
324FPI8/1/2008
324LPLV2/14/2011
324CSR6/23/2011
41FSR7/9/2013
41PA4/15/2013
41FPE7/11/2013
41FPI7/11/2013
41LPLV8/12/2013
41DBL8/12/2013
41CSR10/16/2013
690FSR2/12/2016
690PA8/8/2013
690FPE2/15/2016
690FPI2/26/2016
690LPLV3/18/2016
690DBL6/1/2016
690CSR7/28/2016
596FSR6/27/2013
596PA5/8/2013
596LPLV8/5/2013
596CSR2/13/2014
789FSR10/2/2014
789PA3/10/2014
789FPE10/2/2014
789FPI11/1/2014
789LPLV12/17/2014
789DBL3/27/2015
789CSR5/28/2015
930FSR6/19/2015
930PA5/8/2015
930FPE6/19/2015
930FPI7/15/2015
930LPLV8/26/2015
930DBL9/11/2015
930CSR9/29/2015
44FSR2/7/2016
44PA8/8/2013
44FPE2/8/2016
44FPI2/12/2016
44LPLV3/4/2016
44DBL4/15/2016
44CSR7/28/2016
790FSR10/21/2014
790PA3/10/2014

Here's the sample Data

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.