Greetings, I would like assistance in creating an automated dashboard based on the attached sheet that has certain raw data; this dashboard aims to track the status of orders and measure each person's Efficiency in handling orders and also track aging orders that are still not completed.
Some parameters in this dashboard are mandatory, which are
- Order Number
- Order Type (drop-down list with the following status only (New Business, Replacement, Extension, Investment)
- Parent Organization
- Requesting Organization
- Leader Name
- Order Creation Date
- Order Status (drop-down list with the following status only (Approved, Completed, Onboarding, Interviewed/Interviewing, Rejected/Cancelled, Pending Approval)
- Actual Completion Date (Only when order status is completed)
- Order Age (Calculated automatically based on a formula with the number of days as format, the only instance where I need assistance in this is when the order is completed, age should be empty when it is complete)
- Efficiency in completed (this is the Completion date - Creation Date) (Also days format)
- Order Name
- Order Title
- Client Name
- Pending with whom
So far, this is from a parameters standpoint; for the dashboard, I already have the design ready for what I am looking for specifically. I want to filter these charts with slicers using the parameters below.
- Order Status
When you select a particular leader, you will show the status of their orders, aging orders and Efficiency in completing orders, when you choose a certain status, it will focus on orders for that type of status only.
Finally, it is crucial to view each leader's trends. This brings me to the tricky part: I need a way to track week-on-week changes as well as month vs. month; these sheets are modified each Friday, so I want to compare the orders table this Friday vs. the previous Friday prior to the last modification and the same thing on monthly basis. Is that possible, or is there a more straightforward solution to this? Please let me know if there is a simple way to do this.
Do let me know if you require any further clarifications.