Historical Data Reports

[KG1]

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

[dax]

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 Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KG1]

Hi 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

[dax]

Hi KG1

I am still can't get the average result like you, so if possible, could you please write the expression for me. You could refer to my sample.

In addition , if possible, could you please inform me more detailed information for your second problem? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find i

[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?