Displaying data across production days with Date Slicer

[Diaze2108]

Hi everyone,

I'm encountering a challenge in Power BI and would appreciate any guidance or solutions you might have.

What I'm Trying to Achieve:

I have a dataset that contains information about various production sites. Each site has its own production schedule, specifying the day of the week when production starts and when it ends, including specific start and end times. For example:

Site A:

Production Start: Monday at 06:00 AM

Production End: Tuesday at 03:00 AM

This means that Site A's production day starts on Monday at 6 AM and runs continuously until 3 AM the next day.

In a separate table, I have transactional data that I want to analyze. Each record in this table includes a timestamp (date and time).

My Goal:

I want to create a report where I can analyze the transactional data based on the production day. Specifically, I want to use a date slicer to select a production day (e.g., "October 11, 2024") and have the report display data from 6:00 AM on October 11, 2024, to 3:00 AM on October 12, 2024.

Challenges and Limitations:

Standard Date Slicer Limitations: When I use a standard date slicer and select a single day, it only filters data up to midnight of that day, so I miss the data that occurs after midnight (from 12:00 AM to 3:00 AM the next day).

Variable Production Schedules: Different sites have different production start and end times, and not all production days extend past midnight.

Assigning Timestamps to Production Days: I need a way to correctly associate each timestamp in my transactional data with the appropriate production day, even when the production period spans two calendar days.

What I've Tried So Far:

1. Creating a Calculated Column for Production Date:

I attempted to create a calculated column in my transactional data table that assigns a "Production Date" to each timestamp based on the production schedule.

I used a DAX formula to compare the time part of each timestamp with the production start and end times to determine the production date.

Issue: I encountered errors in the DAX formula, particularly when trying to handle times that extend past midnight (e.g., when the production end time is 3:00 AM the next day). Additionally, when I used this column in the slicer, it still didn't include the data past midnight as desired.

2. Using DAX Measures:

I tried creating DAX measures to calculate whether each timestamp falls within the production window for a selected date.

Issue: Measures can't be used in slicers or to filter the data model directly, so this approach didn't allow me to filter the data as needed.

3. Adjusting the Slicer or Using Custom Slicers:

I considered using a custom slicer or adjusting the date slicer settings to include data past midnight.

Issue: The standard date slicer doesn't support this functionality, and I couldn't find a suitable custom visual that meets this requirement.

4. Exploring Time Intelligence Functions:

I looked into using time intelligence functions to shift the time context or create custom calendars that align with the production schedule.

Issue: These functions didn't seem to address the issue of associating timestamps with a production day that spans across two calendar days.

My Question:

How can I set up my Power BI report so that when I select a production day via a date slicer, it correctly includes all data from the production start time on that day to the production end time, even if it extends past midnight into the next calendar day?

Ideally, I want to:

Assign each timestamp in my transactional data to the correct production day, taking into account the production schedule of each site.

Use a standard date slicer to select a production day, and have the data displayed reflect the full production period for that day.

Handle different production schedules for different sites, as not all sites have the same production start and end times.

[Diaze2108]

Thank you so much! This worked like an absolute charm! 

[Bibiano_Geraldo]

Interresting problem to solve, if you can give us the sample data to see how your table look like it would be great to make tests.

Note: Make sure that the data is sample, not your really data. 

[Diaze2108]

Let me add though, that I'd be happy to find a solution withouth using the second table that includes the location data. If I could solve it by writing the production schedule manually, that would be totally fine 🙂 i just need it to adjust when a filter is used for a different location with a different schedule. 

[Diaze2108]

Hi! I can only write up an example from my phone so I hope this helps. 

Table 1 with columns-

'Location' e.g. 10, 100, 2, 1, ... 

'Actual Arrival' e.g. 20.10.2024 03:00

'Number Plate' e.g. KY23HJ

Table 2 (Location data) with colums-

'Location'- same as in Table 1 and joined by this column

'Production Start' (Time Format) e.g. 06:00:00

'Production End' same as Start

So what I'm working with exactly, is a logistics Yard. These Logistic Centers have their production Schedule and stakeholders want to see data by their schedule. I have trucks going in and out to logistic yards and have all kinds of tables with data gathered by the system used for the yard. 

Is this enough info to work with? Otherwise i might be able to fire up my laptop tomorrow 🙂 

Thanks! 

[Bibiano_Geraldo · Accepted solution]

Lets try together:

1. Create a Table for Production Schedule to look like this:

2. In your transactional table, add this column:

ProductionDay = 
VAR TimeOfDay = TIME(HOUR(Transactions[Timestamp]), MINUTE(Transactions[Timestamp]), SECOND(Transactions[Timestamp]))
VAR ProductionStartTime = LOOKUPVALUE(ProductionSchedule[Production Start], ProductionSchedule[Location], Transactions[Location])
VAR ProductionEndTime = LOOKUPVALUE(ProductionSchedule[Production End], ProductionSchedule[Location], Transactions[Location])
RETURN 
    IF(TimeOfDay >= ProductionStartTime || TimeOfDay <= ProductionEndTime,
        IF(TimeOfDay <= ProductionEndTime, 
            Transactions[Timestamp] - 1,  -- Adjust if after midnight
            Transactions[Timestamp]
        ),
        BLANK()
    )

3. Now, use the ProductionDay column in a slicer to filter data for the appropriate production day.

I hope this help you, make sure to replace columns/tables names with your owns.

Thank you