cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
romovaro
Helper V
Helper V

Customer Life Cicle Process

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 NumberTASKTASK_ASSIGNED_ONTASK_ASSIGNED_ON2TASK_STATUSTASK_COMPLETED_DATETASK_COMPLETED_DATE2
11538Greenlight Checks - CR21-DEC-21 03.16.07.000000 PM21-DEC-21Received  
380263Partner Acknowledgement - 1189541 - 380263AT0115-DEC-21 12.47.16.000000 PM15-DEC-21Received  
380263Start Partner Engagement - 1189541 - 380263AT0114-DEC-21 02.41.42.000000 PM14-DEC-21Closed15-DEC-21 12.47.15.752000 PM15-DEC-21
380263Partner Acknowledgement - 1189541 - 380263PT0114-DEC-21 02.38.57.000000 PM14-DEC-21Received  
380263IPM HandOff - LOA14-DEC-21 09.59.19.000000 AM14-DEC-21Received  
380263Start Partner Engagement - 1189541 - 380263PT0114-DEC-21 09.59.19.000000 AM14-DEC-21Closed14-DEC-21 02.38.49.557000 PM14-DEC-21
11632Greenlight Checks - CR11-DEC-21 11.53.24.000000 AM11-DEC-21Received  
380263Partner Assignment - 1189541 - 380263AT0106-DEC-21 01.22.20.000000 PM06-DEC-21Closed14-DEC-21 02.41.34.239000 PM14-DEC-21
380263Partner Assignment - 1189541 - 380263PT0106-DEC-21 01.22.20.000000 PM06-DEC-21Closed07-DEC-21 10.50.28.589000 AM07-DEC-21
380263Resource Allocation - LOA06-DEC-21 01.20.38.000000 PM06-DEC-21Closed14-DEC-21 09.59.18.751000 AM14-DEC-21
380263Client System Setup - LOA06-DEC-21 01.20.33.000000 PM06-DEC-21Closed06-DEC-21 01.22.16.920000 PM06-DEC-21
380263Greenlight Checks - LOA03-DEC-21 11.32.02.000000 PM03-DEC-21Closed06-DEC-21 01.20.30.862000 PM06-DEC-21
380263Partner Acknowledgement - 997543 - 380263RU0119-OCT-21 10.04.26.000000 AM19-OCT-21Closed19-OCT-21 04.57.21.720000 PM19-OCT-21
380263Start Partner Engagement - 997543 - 380263RU0118-OCT-21 03.56.39.000000 PM18-OCT-21Closed19-OCT-21 10.04.25.678000 AM19-OCT-21
380263Partner Acknowledgement - 997543 - 380263CH0107-OCT-21 05.04.54.000000 PM07-OCT-21Closed11-OCT-21 04.20.48.180000 PM11-OCT-21
380263Start Partner Engagement - 997543 - 380263CH0107-OCT-21 09.40.39.000000 AM07-OCT-21Closed07-OCT-21 05.04.51.678000 PM07-OCT-21
380263Partner Acknowledgement - 997543 - 380263IT0107-OCT-21 09.38.45.000000 AM07-OCT-21Received  
380263Start Partner Engagement - 997543 - 380263IT0106-OCT-21 10.31.06.000000 AM06-OCT-21Closed07-OCT-21 09.38.44.815000 AM07-OCT-21
380263Partner Acknowledgement - 997543 - 380263SE0130-SEP-21 04.59.28.000000 PM30-SEP-21Received  
380263Partner Acknowledgement - 997543 - 380263NO0130-SEP-21 04.59.16.000000 PM30-SEP-21Received  
380263Partner Acknowledgement - 997543 - 380263DK0130-SEP-21 04.59.03.000000 PM30-SEP-21Closed01-OCT-21 09.25.56.566000 AM01-OCT-21
380263Start Partner Engagement - 997543 - 380263SE0130-SEP-21 04.13.21.000000 PM30-SEP-21Closed30-SEP-21 04.59.28.393000 PM30-SEP-21
380263Start Partner Engagement - 997543 - 380263NO0130-SEP-21 04.10.37.000000 PM30-SEP-21Closed30-SEP-21 04.59.16.137000 PM30-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:

_DateDiferrence =
VAR
Sum_Date=Calculate(sumx('BMPS LOF Report','BMPS LOF Report'[TASK_COMPLETED_DATE2]-'BMPS LOF Report'[TASK_ASSIGNED_ON2]))
RETURN
if(sum_Date>=0,Sum_Date,0)
 
Customer Life cycle
 
 The idea is to create something similar to the graph below showing average in days of the different steps of the customer life cycle.
romovaro_0-1642518085463.png

 

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,
 
1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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 =
VAR
Sum_Date=Calculate(sumx('BMPS LOF Report','BMPS LOF Report'[TASK_COMPLETED_DATE2]-'BMPS LOF Report'[TASK_ASSIGNED_ON2]))
RETURN
if(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
)

 

Icey_0-1642750377834.png

 

 
Customer Life cycle
 
 The idea is to create something similar to the graph below showing average in days of the different steps of the customer life cycle.
romovaro_0-1642518085463.png

 

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"?

Icey_0-1642751267610.png

 

 

Best Regards,

Icey

 

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

2 REPLIES 2
romovaro
Helper V
Helper V

 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 

Icey
Community Support
Community Support

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 =
VAR
Sum_Date=Calculate(sumx('BMPS LOF Report','BMPS LOF Report'[TASK_COMPLETED_DATE2]-'BMPS LOF Report'[TASK_ASSIGNED_ON2]))
RETURN
if(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
)

 

Icey_0-1642750377834.png

 

 
Customer Life cycle
 
 The idea is to create something similar to the graph below showing average in days of the different steps of the customer life cycle.
romovaro_0-1642518085463.png

 

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"?

Icey_0-1642751267610.png

 

 

Best Regards,

Icey

 

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

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!