Forum Discussion
Helper IVProject management pipeline calculation
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
Dear MFelix ,
Thanks a lot for your help. It is working properly.
Best Regards,
Kyaw Myo Tun
8 Replies
- MFelix
Super User
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.
KyawMyoTunDear 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
