Track EVENTS IN-PROGRESS between two times

[Schreg]

Greetings,
I have this measure, inspired from Gerhard Brueckl, to track events in-progress between two dates:

this measures works as intended, however, my requirement is to track events in-progress between a start time and end time. So I just modified the measure to point to the appropriate tables/columns:

however, this does not work because the modified measure uses a Time Table in the DATESBETWEEN function:

These are the columns used in the original measure connected to my Date Table:

And these are the columns used in the modified measure connected to my Time Table:

Here is the original PBIX file I used from Gerhard Brueckl’s blog:
https://files.gbrueckl.at/blog/Events-In-Progress_9F14/Events-In-Progress.pbix

@Melissa, hoping you can share your time intelligence super powers

[Melissa]

Hi @Schreg,

You’ll need to replace the DATESBETWEEN function with another functions returning a single column table with ‘Time Table’[Time] values. For example a FILTER over VALUES(‘Time Table’[Time] ).

I hope this is helpful.

[Schreg]

Excellent! But this requires a filter expression, where as the original measure did not filter a table here…
Is this where I should be using MAX(‘Time Table’[Time]) and MIN(‘Time Table’[Time]) variables, like this?

Edit: As it turns out this is very inefficient, seems like I may need to tweak something here:

However, this modified measure does produce some interesting results even though it’s not completely accurate:

This is a Matrix with hours on the rows (0-23) and machine names on the columns, basically counting the hours the machines are busy.

What is the most interesting is that when machines are busy for longer than 24 hours, the measure populates the previous hours:

This machine starts at 8pm on the 17th and ends at 7am on the 20th, and the measure counts backwards from 8pm to 7am as you see here. Obviously this is not the intended results, but I’m much closer to solving this challenge now.

[Melissa]

If you don’t need a filter have you tried replacing it with:
ALL( ‘Time Table’[Time] )

I hope this is helpful

[Schreg]

Hi @Melissa, so I have good news and great news…
Using the VALUES(‘Time Table’[Time]) function with this filter gave me the correct results for about 95% of the cases, which is the great news!!

And good news is I’ve identified the logic for the remaining 5% of cases where the measure is not producing results. I just need help adjusting the query to cover those cases.

So, whenever the end date is in the future and the end time is before the start time, the measure does not produce a result. For example,
Start Date = July 7
Start Time = 10AM
End Date = July 8
End Time = 9AM
This case will not be counted. However, if for example the end time is 11AM, then the case would be counted.

Any ideas on how to troubleshoot this?

[Melissa]

Okay so this doesn’t address your actual problem but does illustrate how you could calculate with date and times exceeding date boundaries.

https://exceleratorbi.com.au/solving-a-complex-time-problem-in-power-bi/

Problem of the Week #3 (DAX) - Timesheet and Wages

[POTW 3 - Page 1 Screenshot] OK, here we go! Problem of the Week #3 is now live! For background on this initiative, check out this post and this week’s launch video that Brian and I did on YouTube. Background on this week’s problem Timesheet data is common to many organizations, and the associated wage costs often are subject to certain rules. Employees are often paid wages based on how many and when hours are worked, and in this exercise, employees are paid as follows: Normal hours (up …

I hope this is helpful

[Schreg]

Still trying to get this to work,
So far I’ve got this measure that’s almost 100% functional, but it is realllllllly sloooooow…
Does anyone have suggestions on how to optimize this?

Ultimately this will go into a matrix visual where the Hours on rows and Machines on columns:

_v6 = 
CALCULATE(
    [# of Jobs],
    FILTER(
        GENERATE(
            SUMMARIZE(
                CALCULATETABLE( Jobs, ALL( 'Time Table'[Hour] ), ALL('Date Table'[Date]) ),
                Jobs[Job Start Date],
                Jobs[Job Start Time],
                Jobs[Job End Date],
                Jobs[Job End Time]
                ),
            FILTER(
                CROSSJOIN(
                    VALUES('Time Table'[Time]),
                    VALUES('Date Table'[Date])
                ),
                AND(
                    'Date Table'[Date] +'Time Table'[Time] >= Jobs[Job Start Date] + Jobs[Job Start Time],
                    'Date Table'[Date] +'Time Table'[Time] <= Jobs[Job End Date] + Jobs[Job End Time]
                )
            )
        ),
        CONTAINS(
            VALUES( 'Time Table'[Time] ),
            'Time Table'[Time],
            'Time Table'[Time]
        )
        && 
        CONTAINS(
            VALUES( 'Date Table'[Date] ),
            'Date Table'[Date],
            'Date Table'[Date]
        )
    )
    ,CROSSFILTER( 'Time Table'[Time], Jobs[Job Start Time], NONE )
    ,CROSSFILTER( 'Date Table'[Date], Jobs[Job Start Date], NONE )
)

[Melissa]

Hi @Schreg,

Glad to read this query is solved, please mark it as such.
For optimization you will have to provide a sample PBIX file, that is required. When you can provide that, create a new topic in the category below:

https://forum.enterprisedna.co/c/optimization-lab/53
.
Here is some information on creating a mock up file.
https://forum.enterprisedna.co/t/tip-tools-and-techniques-for-providing-pbix-files-with-your-forum-questions/17763

[EnterpriseDNA]

Hi @Schreg, did the response provided Melissa help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.