Forum Discussion
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 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
...
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
4 Replies
- amitchandak
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
GWafoninID 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
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
