Cumulative number of units under processing / count time within duration

[vissvess]

Hi,

My data set is as follows.

Date	Unit	Start Time	End Time
20-09-19	GRK9W2	1:53:53 AM	9:05:02 PM
20-09-19	HBRK9W	4:53:17 AM	11:47:37 PM
20-09-19	GBRK9W	5:04:23 AM	1:18:04 PM
20-09-19	DFRK9W	5:53:17 AM	3:48:21 PM
20-09-19	8CRK9W	11:25:00 AM	2:55:04 PM
20-09-19	1KRK9W	1:02:35 AM	2:17:55 AM
20-09-19	2KRK9W	1:00:41 AM	5:27:28 PM
20-09-19	3KRK9W	11:38:31 AM	2:14:31 PM
20-09-19	HGRK9W	1:39:53 AM	5:16:52 PM
20-09-19	4KRK9W	3:29:14 AM	11:28:58 PM
20-09-19	BGRK9W	9:40:37 AM	1:45:38 PM

I also have a date and time table as follows.

With these tables, the relationship is with the date with date and process dataset. Time relation with time table and process table's End Time column.

I need a trend for particular day accross time all open orders at any given time. 

For a unit, between start time and end time, the unit is said to be open order. 

So, at a particular time, the value would be number of open orders, i.e., orders having lesser or equal selected time and greater end time.

Kindly help me in this regard.

Many thanks

[sturlaws · Accepted solution]

Hi @vissvess,

not sure what you are after.
This measure

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Unit] );
    FILTER (
        'Table';
        'Table'[Start Time] <= SELECTEDVALUE ( 'Time'[Time] )
            && SELECTEDVALUE ( 'Time'[Time] ) <= 'Table'[End Time]
    )
)

can be used to show the change in open orders during the day:

regards,
Sturla

[vissvess]

HI @sturlaws ,

Thanks for the wornderful code.

I have a date slicer. With the help of this, I get daily trend over hours. 

But I select a week or more than a day, the numbers gets summed up.

I need either peak of peak of average. A slicer for this to modify the method of aggretation is required. Is it possbile.

Many Thanks

[sturlaws]

Try this

Measure =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Unit] );
        FILTER (
            'Table';
            'Table'[Start Time] <= SELECTEDVALUE ( 'Time'[Time] )
                && SELECTEDVALUE ( 'Time'[Time] ) <= 'Table'[End Time]
        )
    );
    COUNTROWS ( VALUES ( 'Table'[Date] ) )
)

[vissvess]

Hi @sturlaws ,

Thanks for the timely help...

Lemme check for my expected result.

Could you help me in solving similar type of data with some modifications.

Say, I donot have start time. I have something called order which has multiple units.

I have a table named "Master list of order" Which have orders and order quantity. 

Now this process table may contain the following data. Say,

Date --> Order number --> Unit --> Produced time.

I have a measure for the build quantity as calculate(distinctcount(Units)) to count the actual number of units produed againsst the order to full fill the order quantity.

The requirement is same as earlier dataset.

At any point of time, the number of orders that have build quantity not matching with order quantity is to be arrived.

Could you kindly help. I am stranded...

[vissvess]

I have one to many relationship between Masterlist of orders and Process table. Kindly note.

Many thanks for your continual support.

[vissvess]

Initially, I thought the work around would be simple. 

At the end of my day (When I get my daily data), I will have all the orders built (Order quanity = Build Quantity for almost all orders).

So, If I use the above condition I end up in disappointment resulting error or a straight line.

The function may need a time intelligence to neglect the later period orders at any particular period of time to count the open orders.

Hope I can have a better workaround. Thanks in advance @sturlaws .

[sturlaws]

If you can provide a representable sample dataset and mock-up of what you want as outcome, I'll take a look at it

[vissvess]

Hi @sturlaws ,

The outcome is same as I requested earlier and the same as you provided.

The inputs is also the same as before but without start time

Date	Order Number	Unit	Completed Time
20-09-19	82343	GRK9W2	9:05:02 PM
20-09-19	83453	HBRK9W	11:47:37 PM
20-09-19	72354	GBRK9W	1:18:04 PM
20-09-19	83453	DFRK9W	3:48:21 PM
20-09-19	72354	8CRK9W	2:55:04 PM
20-09-19	83453	1KRK9W	2:17:55 AM
20-09-19	83453	2KRK9W	5:27:28 PM
20-09-19	83453	3KRK9W	2:14:31 PM
20-09-19	72354	HGRK9W	5:16:52 PM
20-09-19	72354	4KRK9W	11:28:58 PM
20-09-19	72354	BGRK9W	1:45:38 PM

Now another table - "Masterlist of orders"

Order Number	Order Quantity
72354	8
83453	2
82346	49
92137	15
01235	40

Now, could you read the earlier post for better understanding of the model and requirement.

Again note, the dataset is bit different, but the output required is same.

Thanks again.

[sturlaws]

It is not clear to me what you want your output to look like. Do you want a timeline chart with the number of orders which are not complete yet? Do you want a timeline chart with the number of produced items pr order? If you want a timeline chart, what should the start time be?

[vissvess]

Yes... I want a timeline chart with the number of order which are not complete yet.

There is not start time as in the earlier case. (earlier unit is open between start and end time)

Now the order is open between first and last unit production. Order quanity is available in master table.

At any time, open orders are when the first unit of the order is produced and at the time, the total unit prodcued of the particular order not matching with the specified quantity.

Hope now its clear.

Many Thansk

[sturlaws]

@vissvess, did you solve this?