Forum Discussion

Joe_T's avatar
Joe_T
Regular Visitor
2 years ago

Title: Seeking Help: Creating Dynamic Measures Based on Time Intervals

Hi Power BI Community,

I'm reaching out to seek assistance with creating dynamic measures in Power BI based on time intervals.

Context: I have a dataset named "TIM Tracker (New)" containing information about tasks, including their start and finish times. Additionally, I have generated a table named "Intervals" that lists all 48 30-minute intervals throughout a 24-hour day.

Objective: I need to create dynamic measures for each interval to calculate:

  1. Total minutes worked per task within each interval.
  2. Total count of tasks within each interval.

I have attempted to create measures manually for each interval, but due to the large number of measures required (96), this process is becoming time-consuming and inefficient.

Request for Assistance: I'm seeking guidance on how to dynamically generate these measures based on the existing context. Specifically, I need assistance with DAX expressions or any other techniques that can automate the creation of measures for each interval.

Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Joe_T , You have to create a static interval as column and use that in slicer

    Try a new column like

    30 Min Interval =
    VAR BaseTime = TIMEVALUE('Table'[DateTimeColumn])
    VAR TotalHoursSinceMidnight = HOUR(BaseTime) + MINUTE(BaseTime) / 60.0
    VAR RoundedHours = FLOOR(TotalHoursSinceMidnight, 0.5)
    RETURN
    BaseDate + TIME(HOUR(RoundedHours), MINUTE(RoundedHours * 60), SECOND(0))

    • Joe_T's avatar
      Joe_T
      Regular Visitor
      2 years ago

      amitchandak Thanks for the reply i apprciate your help. This is a solution for part of my query, the part i'm still unsure about relates to visualy presenting this data in a Gantt style chart so durations can be seen across a 24 hour day. I don't know if you can help- i can do this in excel, just not power bi


  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Joe_T ,

    Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

    How to Get Your Question Answered Quickly  

    Regards,

    Xiaoxin Sheng

    • Joe_T's avatar
      Joe_T
      Regular Visitor
      2 years ago

      Good Morning Xiaoxin,

      Yes no problem thanks for looking into this. Please see below for the raw data with names hidden.

      Columns 1-7 is the data i recieve from the warehouse, the interval columns are being pulled, but these were created in the source itself in excel by counting minutes within each interval. This gives me total minutes and count of task per interval (where mins are above 0).

      Please also see below for the gantt style chart i have made in excel. As you can see the interval columns are conditional formatted to highlight active intervals, but i only know how to do this in power bi by creating a measure for each individual column.
      Top view:

      Bottom view:

      For additional context, incase you can help in another way, I want to find out the volume of activity in our warehouse in a standard day. Durations, task volume and start times can then be used to create an efficient recruitment and staffing structure for what we need now, and will enhance my forecasting report (seperate to this), so i can forecast required staffing levels down the line. 
      We are a business that receives, sorts, stores and releases goods for customers ranging from shoes, toys, to pet food-so forecasting is vital for our recruitment as several customers are seasonal and our reaction time needs to be fast.

      If there is any other information you require, please let me know and i will happily share.

      Kind regards,

      Joe




      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        HI Joe_T,

        I'd like to suggest you create a table with time ranges based on interval. 

        NewTable =
GENERATESERIES ( TIME ( 0, 0, 0 ), TIME ( 23, 59, 59 ), TIME ( 0, 15, 0 ) )

        Then you can use this on the matrix column fields and write a measure expression to check the current time value and return flag if the current value is included in the time ranges that defined on your table start, end time fields.

        Flag =
VAR currTime =
    MAX ( NewTable[Value] )
RETURN
    IF (
        currTime >= MIN ( Table1[Start Time] )
            && currTime <= MAX ( Table1[End Time] ),
        1
    )

        After these steps, you can write a formula return color code based on the above flag values to use on 'conditional formatting' feature background color to highlight matched records.

        Apply conditional table formatting in Power BI - Power BI | Microsoft Learn

        Regards,

        Xiaoxin Sheng