Forum Discussion

stribor45's avatar
stribor45
Post Prodigy
1 year ago
Solved

Build Availability Table

Is it possible to build availability table of this data so you can see when there is a time in a week when there are no activities.   Participant Training Time Time Day 1 Day 2 Participa...
Need Help
  • DataNinja777's avatar
    DataNinja777
    1 year ago

    Hi stribor45 ,

    Certainly.  You can add a 30 minutes bucket field in the time table:

    TimeTable = 
ADDCOLUMNS(
    GENERATESERIES(0, 1439, 1),
    "Time", TIME(INT([Value] / 60), MOD([Value], 60), 0),
    "Hour", INT([Value] / 60),
    "Minute", MOD([Value], 60),
    "Hour-Minute", FORMAT(TIME(INT([Value] / 60), MOD([Value], 60), 0), "hh:mm"),
    "30-Minute Bucket", 
        FORMAT(
            TIME(INT([Value] / 60), 
            IF(MOD([Value], 60) < 30, 0, 30), 0), 
            "hh:mm"
        )
)

    Then write a measure like below:

    Participants during Duration 30Min = 
VAR SelectedTime = MAX('TimeTable'[Time]) // Assume Time is in "HH:MM" format
VAR BucketStart = TIME(HOUR(SelectedTime), IF(MINUTE(SelectedTime) < 30, 0, 30), 0)
VAR BucketEnd = IF(MINUTE(SelectedTime) < 30, 
                   BucketStart + TIME(0, 30, 0), 
                   BucketStart + TIME(1, 0, 0))

// Concatenate participants within the time window
VAR ParticipantsList = 
    CONCATENATEX(
        FILTER(
            DISTINCT('Table'),
            'Table'[Start Time] <= BucketEnd &&
            'Table'[End Time] >= BucketStart
        ),
        RIGHT('Table'[Participant], 1),
        ", "
    )

// Return either the list of participants or '〇' if no participants
RETURN
    IF(
        ISBLANK(ParticipantsList),
        "-", // Display "-" when no participants
        ParticipantsList
    )

    You can use the measure above alongside a color measure in conditional formatting to achieve a visualization shown at the bottom.

    Color = 
IF ([Participants during Duration 30Min]="-","#90EE90","#FF0000")
//the color can be replaced with hexadecimal codes like #6B2328)))

     

    I have attached an example pbix file for your reference.

    Best regards,

     

     

stribor45's avatar
stribor45
Post Prodigy
1 year ago

DataNinja777   Thank you for sharing this. "driving" with its additional activity called "first aid" is an exampke  but other actiivties may or may not have additional activity scheduled at different day/time then the main activity.  Would this work as well on this solution?

I was trying to study the example you attached but i noticed that for participant #1 

#1 first aid on wednesday 11 to 12
driving on monday and ewednesday from 7-8 pm
swiming on monday and wednesday from 8:30 to 9:30 am

 

but the output starts half an hour earlier on  for first aid and driving

 

 

DataNinja777's avatar
DataNinja777
Super User
1 year ago

Hi stribor45 ,

 

Thank you for checking.  I tweaked the formula a little bit to fix the inaccuracy in output.  

Participants during Duration 30Min = 
VAR SelectedTime = MAX('TimeTable'[Time]) // Assume Time is in "HH:MM" format
VAR BucketStart = TIME(HOUR(SelectedTime), IF(MINUTE(SelectedTime) < 30, 0, 30), 0)
VAR BucketEnd = IF(MINUTE(SelectedTime) < 30, 
                   BucketStart + TIME(0, 30, 0), 
                   BucketStart + TIME(1, 0, 0))

// Concatenate participants within the time window
VAR ParticipantsList = 
    CONCATENATEX(
        FILTER(
            DISTINCT('Table'),
            'Table'[Start Time] <= BucketStart &&
            'Table'[End Time] > BucketStart
        ),
        RIGHT('Table'[Participant], 1),
        ", "
    )

// Return either the list of participants or '-' if no participants
RETURN
    IF(
        ISBLANK(ParticipantsList),
        "-", // Display "-" when no participants
        ParticipantsList
    )

 

Specifically changed part is as shown below where instead of BucketEnd, BucketStart variable was used.  

Using only BucketStart in the filter keeps the formula straightforward and avoids errors caused by unnecessary checks with BucketEnd. This approach reliably captures participants within each 30-minute interval as intended.

The output is displayed below, and a sample check has confirmed that it now shows the correct results.

 

 

I have attached the revised pbix file for your reference.

 

Best regards,