Forum Discussion
Counting individual orders
Hi,
Assuming that the table name of the screenshot in your last post is Production and you want to summarize the production in a new table called "ProductionOrderSummary", the following are the DAX codes.
Step 1: Create a calculated table called ProductionOrderSummary
ProductionOrderSummary = DISTINCT(Production[UniqueProductionOrder])
Step 2: Add the start time column.
Add a calculated column to ProductionOrderSummary table you have created with the following code.
StartTime = MINX(FILTER(ALL(Production),Production[UniqueProductionOrder]=EARLIER(ProductionOrderSummary[UniqueProductionOrder])),Production[Begintijd def])
Step 3: Add the end time column.
Add a calculated column to ProductionOrderSummary table you have created with the following code.
EndTime = MAXX(FILTER(ALL(Production),Production[UniqueProductionOrder]=EARLIER(ProductionOrderSummary[UniqueProductionOrder])),Production[Eindtijd def])
Step 4: Add the total quantity
Add a calculated column to ProductionOrderSummary table you have created with the following code.
Quantity = SUMX(FILTER(ALL(Production),Production[UniqueProductionOrder] = EARLIER(ProductionOrderSummary[UniqueProductionOrder])),Production[Qty])
Please change the table names and field names in the codes above as applicable in your case.
Much appreciated, I will wait for your answer and try to understand the logic meanwhile.
Hi,
Assuming that the table name of the screenshot in your last post is Production and you want to summarize the production in a new table called "ProductionOrderSummary", the following are the DAX codes.
Step 1: Create a calculated table called ProductionOrderSummary
ProductionOrderSummary = DISTINCT(Production[UniqueProductionOrder])
Step 2: Add the start time column.
Add a calculated column to ProductionOrderSummary table you have created with the following code.
StartTime = MINX(FILTER(ALL(Production),Production[UniqueProductionOrder]=EARLIER(ProductionOrderSummary[UniqueProductionOrder])),Production[Begintijd def])
Step 3: Add the end time column.
Add a calculated column to ProductionOrderSummary table you have created with the following code.
EndTime = MAXX(FILTER(ALL(Production),Production[UniqueProductionOrder]=EARLIER(ProductionOrderSummary[UniqueProductionOrder])),Production[Eindtijd def])
Step 4: Add the total quantity
Add a calculated column to ProductionOrderSummary table you have created with the following code.
Quantity = SUMX(FILTER(ALL(Production),Production[UniqueProductionOrder] = EARLIER(ProductionOrderSummary[UniqueProductionOrder])),Production[Qty])
Please change the table names and field names in the codes above as applicable in your case.
Thanks so much, understand the logic now : )
Anonymous,
The UniqueProductionOrder is
UniekeOrder = CONCATENATE(RegistratieMaasland[Lijn]&RegistratieMaasland[Shift]&" ";RegistratieMaasland[Subnummer]&" "&RegistratieMaasland[Datum])
- Line number
- Shift number
- Subnumber
- Datenumber
What happens next is that the start time and end time are incorrect when an order switches between midnight. Do you have a solution for this?
Didnt include the second picture
Anonymous any idea?
Hi,
I have't understood why you are getting incorrect Start Time and End Time. When I suggested a solution, I assumed that the fields "Begintijd def" and "Eindtijd def" are of fields with data types as "Date/Time". That is what you have shown in your sample data.
We have applied MAX and MIN on "Date/Time" data type and not on "Time" data type.
So logically, for any unique order, the start time calculated using MIN on "Date/Time" values will always pickup the earliest date-time combination for the order and similarly, the end time calculated using MAX on "Date/Time" values will always pickup the last date-time combination from the available multiples values for the unique order.
The problem is when you split the "Date/Time" data type column into "Date" and "Time" separately and then apply the MIN and MAX on "Time" field.
Is my understanding correct? If yes, you can modify the Start and End time and apply it on "Date/Time" fields.
Eg:
Unique Order
Begintijd def
Eindtijd def
A
28-9-2018 10:47:00
28-9-2018 11:38:00
A
28-9-2018 11:38:00
29-9-2018 00:05:00
In this example given in the table above,
MIN/MINX on Begintijd def will give you 28-9-2018 10:47:00 as StartTime
MAX/MAXX on Eindtijd def will give you 29-9-2018 00:05:00 as EndTime
Even though the start-time and end-time are in different dates, the formula will still give you correct results because internally date time is stored as a decimal number with the integer part representing the date and the decimal part representing the seconds where 1 second is represented as 1/(24*60*60). So irrespective of time, if you use a date-time data type for these columns, the Start Time and End Time will be correct.
If you are not getting the correct results, please send me a sample data excactly showing the scenario where you are getting wrong results.
Anonymous
The setting were correct, so I am sending you some sample date.
ProductieID Flowpacks geproduceerd totaal Maasland Begintijd def Eindtijd def Shift Date Subnummer
7077 681 27-9-2018 1:41:19 27-9-2018 2:05:41 a 27-9-2018 300-1140
7127 6.844 27-9-2018 21:58:14 28-9-2018 1:14:36 a 27-9-2018 300-1140UniekeOrder Doorlooptijd order QuantityTotal StartTime EndTime
1 a 300-1140 27-9-2018 23,55 7.525 01:41:19 01:14:36So basically what happened is that 27-9 production made a small order of 681 flowpacks in the night. Then in the evening of 27-9 at 21:58 production started an order with an endtime of 1:14 at 28-9-2018.
I think what is missing is a unique production order key. Would you have an idea how to set this up?