Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Power Bi,
I need to create a graph showing the "circle fo life - Average in days" from different tasks
I have the table below with different tasks + Date assigned + Task status + Date Task completed.
Client Number | TASK | TASK_ASSIGNED_ON | TASK_ASSIGNED_ON2 | TASK_STATUS | TASK_COMPLETED_DATE | TASK_COMPLETED_DATE2 |
11538 | Greenlight Checks - CR | 21-DEC-21 03.16.07.000000 PM | 21-DEC-21 | Received | ||
380263 | Partner Acknowledgement - 1189541 - 380263AT01 | 15-DEC-21 12.47.16.000000 PM | 15-DEC-21 | Received | ||
380263 | Start Partner Engagement - 1189541 - 380263AT01 | 14-DEC-21 02.41.42.000000 PM | 14-DEC-21 | Closed | 15-DEC-21 12.47.15.752000 PM | 15-DEC-21 |
380263 | Partner Acknowledgement - 1189541 - 380263PT01 | 14-DEC-21 02.38.57.000000 PM | 14-DEC-21 | Received | ||
380263 | IPM HandOff - LOA | 14-DEC-21 09.59.19.000000 AM | 14-DEC-21 | Received | ||
380263 | Start Partner Engagement - 1189541 - 380263PT01 | 14-DEC-21 09.59.19.000000 AM | 14-DEC-21 | Closed | 14-DEC-21 02.38.49.557000 PM | 14-DEC-21 |
11632 | Greenlight Checks - CR | 11-DEC-21 11.53.24.000000 AM | 11-DEC-21 | Received | ||
380263 | Partner Assignment - 1189541 - 380263AT01 | 06-DEC-21 01.22.20.000000 PM | 06-DEC-21 | Closed | 14-DEC-21 02.41.34.239000 PM | 14-DEC-21 |
380263 | Partner Assignment - 1189541 - 380263PT01 | 06-DEC-21 01.22.20.000000 PM | 06-DEC-21 | Closed | 07-DEC-21 10.50.28.589000 AM | 07-DEC-21 |
380263 | Resource Allocation - LOA | 06-DEC-21 01.20.38.000000 PM | 06-DEC-21 | Closed | 14-DEC-21 09.59.18.751000 AM | 14-DEC-21 |
380263 | Client System Setup - LOA | 06-DEC-21 01.20.33.000000 PM | 06-DEC-21 | Closed | 06-DEC-21 01.22.16.920000 PM | 06-DEC-21 |
380263 | Greenlight Checks - LOA | 03-DEC-21 11.32.02.000000 PM | 03-DEC-21 | Closed | 06-DEC-21 01.20.30.862000 PM | 06-DEC-21 |
380263 | Partner Acknowledgement - 997543 - 380263RU01 | 19-OCT-21 10.04.26.000000 AM | 19-OCT-21 | Closed | 19-OCT-21 04.57.21.720000 PM | 19-OCT-21 |
380263 | Start Partner Engagement - 997543 - 380263RU01 | 18-OCT-21 03.56.39.000000 PM | 18-OCT-21 | Closed | 19-OCT-21 10.04.25.678000 AM | 19-OCT-21 |
380263 | Partner Acknowledgement - 997543 - 380263CH01 | 07-OCT-21 05.04.54.000000 PM | 07-OCT-21 | Closed | 11-OCT-21 04.20.48.180000 PM | 11-OCT-21 |
380263 | Start Partner Engagement - 997543 - 380263CH01 | 07-OCT-21 09.40.39.000000 AM | 07-OCT-21 | Closed | 07-OCT-21 05.04.51.678000 PM | 07-OCT-21 |
380263 | Partner Acknowledgement - 997543 - 380263IT01 | 07-OCT-21 09.38.45.000000 AM | 07-OCT-21 | Received | ||
380263 | Start Partner Engagement - 997543 - 380263IT01 | 06-OCT-21 10.31.06.000000 AM | 06-OCT-21 | Closed | 07-OCT-21 09.38.44.815000 AM | 07-OCT-21 |
380263 | Partner Acknowledgement - 997543 - 380263SE01 | 30-SEP-21 04.59.28.000000 PM | 30-SEP-21 | Received | ||
380263 | Partner Acknowledgement - 997543 - 380263NO01 | 30-SEP-21 04.59.16.000000 PM | 30-SEP-21 | Received | ||
380263 | Partner Acknowledgement - 997543 - 380263DK01 | 30-SEP-21 04.59.03.000000 PM | 30-SEP-21 | Closed | 01-OCT-21 09.25.56.566000 AM | 01-OCT-21 |
380263 | Start Partner Engagement - 997543 - 380263SE01 | 30-SEP-21 04.13.21.000000 PM | 30-SEP-21 | Closed | 30-SEP-21 04.59.28.393000 PM | 30-SEP-21 |
380263 | Start Partner Engagement - 997543 - 380263NO01 | 30-SEP-21 04.10.37.000000 PM | 30-SEP-21 | Closed | 30-SEP-21 04.59.16.137000 PM | 30-SEP-21 |
First problem I have.
I need to see the difference in days. But sometimes the date Task completed is missing...and should be the difference between the Assigned Date until today... With the formula it shows 0 days....but that's wrong
Formula I currenly use for the difference in dates:
With the diff dates I can create the graph with average days but I have issues adding the status i need and showing the steps in the correct order. Any idea how to include a formula that will help me to filter the steps?
Solved! Go to Solution.
Hi @romovaro ,
First problem I have.
I need to see the difference in days. But sometimes the date Task completed is missing...and should be the difference between the Assigned Date until today... With the formula it shows 0 days....but that's wrong
Formula I currenly use for the difference in dates:
_DateDiferrence =VARSum_Date=Calculate(sumx('BMPS LOF Report','BMPS LOF Report'[TASK_COMPLETED_DATE2]-'BMPS LOF Report'[TASK_ASSIGNED_ON2]))RETURNif(sum_Date>=0,Sum_Date,0)
Try this:
_DateDiferrence =
DATEDIFF (
MAX ( 'BMPS LOF Report'[TASK_ASSIGNED_ON2] ),
IF (
ISBLANK ( MAX ( 'BMPS LOF Report'[TASK_COMPLETED_DATE2] ) ),
TODAY (),
MAX ( 'BMPS LOF Report'[TASK_COMPLETED_DATE2] )
),
DAY
)
Customer Life cycleThe idea is to create something similar to the graph below showing average in days of the different steps of the customer life cycle.
With the diff dates I can create the graph with average days but I have issues adding the status i need and showing the steps in the correct order. Any idea how to include a formula that will help me to filter the steps?
Steps:1 . Greenlights checks (status Closed)2. Pre Engagement Process (status Received)3. Partner Assignment (status received and closed)4. IPM hand Off (status Received)5 . IPM Hand Off (status Closed)6. Start Partner Engagement (status received and closed)7. Partner Acknowledgement (status received and closed)Thanks,
For another issue, do you mean you want to rank the "Task" column based on the order you provided? In addition, does it need to group by different "Client Number"?
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Icey
Regarding the second problem:
For another issue, do you mean you want to rank the "Task" column based on the order you provided? In addition, does it need to group by different "Client Number"?
No, I don't need to group by client number. As just want to get the average day x step and create a graph showing how many days (average) takes the customer for every step of the journey until
Hi @romovaro ,
First problem I have.
I need to see the difference in days. But sometimes the date Task completed is missing...and should be the difference between the Assigned Date until today... With the formula it shows 0 days....but that's wrong
Formula I currenly use for the difference in dates:
_DateDiferrence =VARSum_Date=Calculate(sumx('BMPS LOF Report','BMPS LOF Report'[TASK_COMPLETED_DATE2]-'BMPS LOF Report'[TASK_ASSIGNED_ON2]))RETURNif(sum_Date>=0,Sum_Date,0)
Try this:
_DateDiferrence =
DATEDIFF (
MAX ( 'BMPS LOF Report'[TASK_ASSIGNED_ON2] ),
IF (
ISBLANK ( MAX ( 'BMPS LOF Report'[TASK_COMPLETED_DATE2] ) ),
TODAY (),
MAX ( 'BMPS LOF Report'[TASK_COMPLETED_DATE2] )
),
DAY
)
Customer Life cycleThe idea is to create something similar to the graph below showing average in days of the different steps of the customer life cycle.
With the diff dates I can create the graph with average days but I have issues adding the status i need and showing the steps in the correct order. Any idea how to include a formula that will help me to filter the steps?
Steps:1 . Greenlights checks (status Closed)2. Pre Engagement Process (status Received)3. Partner Assignment (status received and closed)4. IPM hand Off (status Received)5 . IPM Hand Off (status Closed)6. Start Partner Engagement (status received and closed)7. Partner Acknowledgement (status received and closed)Thanks,
For another issue, do you mean you want to rank the "Task" column based on the order you provided? In addition, does it need to group by different "Client Number"?
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |