Forum Discussion
Resolver IHistorical Data Reports
Hi,
Would someone be able to kindly help me with this problem
I have a data table which contains the created date and completion date of a unique work order
I need to look back over time and report on the status of the work basket at a particular point in time
I have supplied a snapshot of dummy data I created in Excel
| Work Order Number | Created Date | Actual End | Time to Complete |
| 12 | 15/08/2019 | 21/08/2019 | 6 |
| 123 | 15/08/2019 | ||
| 234 | 16/08/2019 | 19/08/2019 | 3 |
| 345 | 16/08/2019 | ||
| 456 | 16/08/2019 | 03/09/2019 | 18 |
| 567 | 16/08/2019 | ||
| 678 | 16/08/2019 | 20/08/2019 | 4 |
| 789 | 16/08/2019 | 21/08/2019 | 5 |
| 900 | 16/08/2019 | 20/09/2019 | 35 |
| 1011 | 16/08/2019 | 20/08/2019 | 4 |
| 1122 | 16/08/2019 | 20/08/2019 | 4 |
| 1233 | 16/08/2019 | 19/08/2019 | 3 |
| 1344 | 02/09/2019 | 04/09/2019 | 2 |
| 1455 | 02/09/2019 | ||
| 1566 | 02/09/2019 | 02/09/2019 | 0 |
| 1677 | 02/09/2019 | 03/09/2019 | 1 |
| 1788 | 03/09/2019 | 05/09/2019 | 2 |
| 1899 | 03/09/2019 | ||
| 2010 | 03/09/2019 | 09/09/2019 | 6 |
| 2121 | 03/09/2019 | 06/09/2019 | 3 |
| 2232 | 03/09/2019 | 18/09/2019 | 15 |
| 2343 | 03/09/2019 | ||
| 2454 | 03/09/2019 | 03/09/2019 | 0 |
| 2565 | 03/09/2019 |
I need to able to show the following for each month
For August 19:
Count the number of orders not completed before month end (ignore any completed >01/09/19)
The average cycle time of all the orders that were completed before month end
For Sept
This will now become a cumulative number and include all the orders released in August as well
Count the total number of orders not completed before month end (ignore any completed >01/10/19)
The average cycle time of all the orders that were completed before month end
Expected Result
| August | Sept | |
| No of Oustanding Jobs | 4 | 7 |
| Average Cycle time | 7.25 | 15.8571429 |
Many Thanks
4 Replies
daxCommunity Support
Hi KG1,
If possible, could you please explain the logic to me? Count the number of orders not completed before month end in Augest should be 3
In addition, I want to know how you calculate the Average Cycle time? could you please explain to me?
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
KG1Hi Zoe
Thank you for taking the time to resond
Apologies - yes you are right it should be 3 orders not completed for August
The cycle time is calcuated by totalling the time to complete and dividing by the total number of orders
Many Thanks
- KG1
So my work so far
I have created 2 new columns for the created date and actual end date which extracts the Month
Completed Month = Format([Actual End].[Month],"MMM")
I have exported this data into Excel to try and work out the formula for the table visual
I have 2 tables 1. Total Orders Created 2. Total Orders Completed
Month Total Orders Created August 1291 September 1784 Grand Total 3075 Month Total Orders Completed August 479 September 1921 Outstanding Grand Total 2400 The output table would be
August Work Basket 812 September Work Basket 675 August Formula = 1291-479 = 812
Sept Formula = (1784+812)-1921 = 675
How do I replicate this in PowerBI?
Or have I gone about this totally the wrong way?