Forum Discussion
GWafonin
Advocate III
Advocate IIICycle time matrix calculation (distance in days between dates)
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...
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
Super User
Super UserGWafonin ,
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
GWafoninAdvocate III
Advocate IIIID 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