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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
romovaro
Post Partisan
Post Partisan

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
Post Partisan
Post Partisan

 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.