Filtering and splitting a value based on date range

[kleighton]

Hi Community!

So, i have 2 tables containing manufacturing data

1, Runtime table - Contains the Runtime data of machines (start, error, wait etc) with timestamps of each event

2, Work table - Contains a list of jobs (by ordernumber) which have run on the machines including start and end timestamps for each job

My goal is to match the ordernumber from Work table to the Runtime table based on Timestamp so i can report on the hrs. each machine is in each state per job (ordernumber).

my idea was adding a column to the Runtime table as follows: This checks first for matching machineid (there will be many machines with different jobs running at the same time, but for this sample i just have one) then matches the start and end time of the runtime event to the start and endtime of the job 

ordernumber =

CALCULATE(

VALUES('Work'[Column1.OrderNumber]),

FILTER(ALL('Work'), Runtime[Column1.machineId]='Work'[machineid] && Runtime[Column1.startTime]>=('Work'[Column1.StartDate]) && Runtime[Column1.endTime]<=('Work'[Column1.EndDate])))

This works as expected and brings the ordernumber to the runtime table. However, what i didnt think about is that it of course does not handle the periods when jobs have changed over, which could happen in the middle of the machine running or stopped state. Which then gives a blank in the ordernumber column as it happens during a state.

Question: what is the best way to handle this? 

Sample file attached

[v-kkf-msft]

Hi @kleighton ,

I calculate the ordernumber corresponding to the periods when jobs have changed over to two job numbers. The formula for the created calculated column is as follows:

ordernumber = 
var MinDate = 
    MINX(
        FILTER(
            'Work',
            'Work'[machineid] = Runtime[Column1.machineId]
            && 'Work'[Column1.EndDate] > Runtime[Column1.endTime]
        ),
        'Work'[Column1.EndDate]
    )
var MaxDate = 
    MAXX(
        FILTER(
            'Work',
            'Work'[machineid] = Runtime[Column1.machineId]
            && 'Work'[Column1.EndDate] < Runtime[Column1.endTime]
        ),
        'Work'[Column1.EndDate]
    )
var OrderNum = 
    CALCULATE(
        VALUES('Work'[Column1.OrderNumber]),
        FILTER(
            ALL('Work'), 
            'Work'[machineid] = Runtime[Column1.machineId]
            && 'Work'[Column1.StartDate] <= Runtime[Column1.startTime]
            && 'Work'[Column1.EndDate] >= Runtime[Column1.endTime]
        )
    )
var MaxOrder = 
    CALCULATE(
        VALUES('Work'[Column1.OrderNumber]),
        FILTER('Work','Work'[machineid] = Runtime[Column1.machineId] && 'Work'[Column1.EndDate] = MinDate)
    )
var MinOrder = 
    CALCULATE(
        VALUES('Work'[Column1.OrderNumber]),
        FILTER('Work','Work'[machineid] = Runtime[Column1.machineId] && 'Work'[Column1.EndDate] = MaxDate)
    )
return 
    IF( ISBLANK(OrderNum), CONCATENATE(MinOrder,MaxOrder), OrderNum )

 

Is this what you want?

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[kleighton]

Hi Winniz,

Apologies for the late reply. Thanks for this, it is working as you described, but does not resolve what i need.

So as you seen from the sample file, i want to report on the Duration per Ordernumber the machine has been in different states (Running, error etc).

whilst your solution has now brought the associated Order numbers before and after the work changeover (Thanks again), it still doesnt help me get the specific duration for each of them.

I suppose this calculation may need to be stored in a new table as it will create additional rows of data by splitting the duration into its relevant Ordernumbers?..

Thanks in advance,

Keith

[lbendlin]

Generally the recommendation is to use an INTERSECT() - that is, if you can agree on some sort of granularity level like 5 minute intervals.  If you need exact timings then the DAX becomes much more complex. I have similar reports where the logic is so complex that I have to do it outside of Power BI (as a SQL Server scalar function).

Please explain your design requirements/limitations.