dynamic table creation

Hi MichaelPercival ,

I think your need is to create a dynamic scheduling system that can then be dynamically selected based on dates, rather than creating a table of all personnel data.

We begin by creating a date table with which to filter.

Date table = CALENDAR(DATE(2024,6,1),DATE(2024,7,31))

And then we create a slicer to filter on dates to dynamically select the status of each employee.

ShiftOnSelectedDate = 
VAR selectedDate = MAX('Date table'[Date])
RETURN
CALCULATE(
   FIRSTNONBLANK('shifts_large 1'[Shift], 1),
   FILTER('shifts_large 1', 'shifts_large 1'[StartDate] <= selectedDate && 'shifts_large 1'[EndDate]>= selectedDate)
)

AvailabilityOnSelectedDate = 
VAR selectedDate = MAX('Date table'[Date])
RETURN
CALCULATE(
   FIRSTNONBLANK('availability_large'[Availability], 1),
   FILTER('availability_large', 'availability_large'[StartDate] <= selectedDate && 'availability_large'[EndDate] >= selectedDate)
)

HolidayOnSelectedDate = 
VAR selectedDate = MAX('Date table'[Date])
RETURN
CALCULATE(
   FIRSTNONBLANK('holidays_large (1)'[HolidayType], 1),
   FILTER('holidays_large (1)', 'holidays_large (1)'[StartDate] <= selectedDate && 'holidays_large (1)'[EndDate] >= selectedDate)
)

If you still have questions about this, check out my attachment, I hope it helps.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Hi MichaelPercival ,
For this kind of problem, we can the MEASURES I have written to perform a merge into a VALUE value for the operation.

CombinedInfo = 
VAR shift = [ShiftOnSelectedDate]
VAR holiday = [HolidayOnSelectedDate]
VAR availability = [AvailabilityOnSelectedDate]
RETURN
CONCATENATE(
   CONCATENATE(shift, IF(shift <> "" && holiday <> "", "; ", "")),
   CONCATENATE(holiday, IF((shift <> "" || holiday <> "") && availability <> "", "; ", "") & availability)
)

Remove column totals and row totals to make it more relevant to your needs.

In this case, you can also use Power BI's conditional formatting to go ahead and set it up so that the formatting in your Power BI is equivalent to the formatting in your execl.
Here is the documentation I found for you hope it helps.

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

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.