Calculate available time from resource table, resource_availability dim and bookings fact tables

To calculate the available time for resources in Power BI based on the given tables and requirements, you can follow these steps:

Assuming you have the following tables:

1. Resource Table

   • Columns: Id, Name

2. Resource_availability Table

   • Columns: ResourceID, Weekday, WorkingHoursPerDay

3. Bookings Fact Table

   • Columns: BookingID, ResourceID, Category, Weekday

Here's a step-by-step guide:

1. Create Relationships:

   • In Power BI Desktop, go to the "Model" view.
   • Create relationships between the tables:
     • Resource Table.Id to Resource_availability Table.ResourceID
     • Resource Table.Id to Bookings Fact Table.ResourceID

2. Create Calculated Columns:

   • In the Resource_availability Table, create a calculated column to convert WorkingHoursPerDay to minutes:

WorkingMinutesPerDay = Resource_availability[WorkingHoursPerDay] * 60

1. Create a Calendar Table:

   • Create a calendar table that includes all the dates in the selected month (November 2023).
   • Ensure this table has a relationship with the Bookings Fact Table based on the Weekday column.

2. Calculate Total Minutes Available:

   • In the Bookings Fact Table, create a calculated column to calculate the total minutes available for each booking:

TotalMinutesAvailable =
RELATED(Resource_availability[WorkingMinutesPerDay])
- IF(Bookings[Category] = "Unavailable", RELATED(Resource_availability[WorkingMinutesPerDay]), 0)

Aggregate Available Time:

• Create a new table or a matrix visualization that shows the aggregated available time for each resource:

TotalAvailableTime = SUM(Bookings[TotalMinutesAvailable])

1. Apply Filters:

   • Apply the necessary filters to restrict the results to the selected month (November 2023).

This approach assumes that you want to calculate the available time based on the selected month in your report. Adjust the calculations and filters according to your specific needs.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.