cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kleighton
Helper I
Helper I

Filtering and splitting a value based on date range

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? 
 
 
3 REPLIES 3
v-kkf-msft
Community Support
Community Support

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 )

 image.png

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.

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).

kleighton_0-1618569513260.png

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
Super User
Super User

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.