Project management pipeline calculation

[KyawMyoTun]

Dear Experts,

      I'd like to get some help on the project management pipeline.
My data source is like below.
2nd Week

3rd Week

Which are individual workbook and I'll upload from folder.
I would like to get
1- To get the only one Expected Revenue based on latest date.
2- To get Duration(Days) which stand on each status. (e.g F001 stays 3 days in Lead and Proposal is 10 days calculated by today)
3- Visualized pipeline on each status start date and end date.

Expected Result

Thanks, and I am looking forward your suggestion.

Best Regards,
Kyaw Myo Tun

[MFelix]

Hi @KyawMyoTun ,

What I did was the following:

• Splited the tables into Project and Pipelines
• On the Pipelines did the following steps:
  • Sorted columns by Project Code and Then Status Changed Date
  • Added index column
  • Add two customs columns with the followings syntax

END DATE:

try if #"Added Index"[Project Code]{[Index] + 1}  = [Project Code] then  #"Added Index"[Status Change Date]{[Index] + 1}   else DateTime.Date (DateTime.LocalNow()) otherwise DateTime.Date (DateTime.LocalNow())

DURATION:

Duration.TotalDays ( [End Date] - [Status Change Date])

• Created a matrix visualization with the following setup:
  • Rows: Corporate Name
  • Columns: Status:
  • Values:
    • Expected Revenue
    • Duration
    • Status Changed
    • End Date

Check PBIX file attach.

[KyawMyoTun · Accepted solution]

Dear @MFelix ,

   Thanks a lot for your help. It is working properly.

Best Regards,
Kyaw Myo Tun

[KyawMyoTun]

Dear @MFelix ,

    Can you please help me one more thing?
Actually I have 3 more status and I'd like to get the status transition count based on status change date.
In my current event, the status trisition happened from Lead to Proposal.
So, I'd like to count as "Lead to Prosoal =1"
Can you please help me on this also? Thanks.

Best Regards,
Kyaw Myo Tun

[MFelix]

Hi @KyawMyoTun,

How do you know the order of the status is by the date?

Maybe the easiest way is to calculate the number of the status?

If you go always from lead to proposal if you have 10 projects with proposal status that would give you that 10 went from one state to the other correct? 

[KyawMyoTun]

Dear @MFelix ,

   I am so sorry for my late reply.
We have 6 status actually.
1. Lead
2. Proposal

3. Negotiation
4. Loss

5. Contract
This will not always the same order that "Lead" can change to "Nego" or "Loss" or "Contract".
e.g if a project start from "Lead" then "Proposal" then "Negotiation" then "Contract",
we'll count as "Lead to Proposal" =1, "Proposal" to Negotiation", etc..
It will show under the same project code.
I hope this help to understand my issue. Thanks.

Best Regards,
Kyaw Myo Tun

[MFelix]

Hi @KyawMyoTun ,

If the case is that thing may change without a specific order thebn you should calculate the number of projects in a certain state:

Has you can see on the cards there were two projects that change to the state of Proposal and 1 to the state of Negotiation.

I don't know if this is what you want to achieve but using the count of status should give you the expected result.

[KyawMyoTun]

Dear @MFelix ,

   Thank a lot for your reply and help.
I think the count of each status can only give me the existing situation of status.
But I also want to get the which previous status is coming to existing status.
e.g P001  initial status is "Lead" and changed to "Negotiation".
      P002 initial status is "Proposal" and changed to "Negotiation".
If I count the Negotiation, I can only get count(2). But I like to get as
"Lead to Negotiation" =1
"Proposal to Negotiation" = 1
I hope you will understand on my requirement.
Thanks.

Best Regards,
Kyaw Myo Tun

[KyawMyoTun]

Dear @MFelix ,

   I'd like to ask you for one more help.
I would like to get the weekly updates for those transactions.
Week is defined by Report Date.
1 - If the project code is from previous week and status is the same (e.g F002), I'd like to count as Week 1 transaction.
So, the week1 transactions are 2
2- If the project code is from previous week but status has changed (e.g F001),
I'd like to count that in week2.
So, the week2 transactions are 3.
3- If I select both Week1 and Week2,
the transaction count should be only 1 for F001(Distinct Count)


Thanks,
Kyaw Myo Tun