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

[anusha_2023]

Resource Table has columns Id and name. Resource_availability table has columns resource ID, weekday mentioning which weekday from Monday to Sunday so each resource ID has seven rows, working hours per day. Naturally, these two dimension tables are connected from one to many. Now I have a fact table bookings which has booking ID, resource ID, category, and weekday as columns. Bookings and Resource tables are connected with one to many. Now I need to calculate the available time of resources by subtracting the working hours per day from resource_avaibility and unavailable time is one of the categories from the bookings table.

For example, if I have a filter selected month November 2023 then I need to get the available time of all resources.

[anusha_2023]

This is my model and if I calculate TotalMinutesAvailable as per your suggested below formula
RELATED(Resource_availability[WorkingMinutesPerDay])
- IF(Bookings[Category] = "Unavailable", RELATED(Resource_availability[WorkingMinutesPerDay]), 0)

The calculation formula for total available hours you understood correctly. Then I have total available hours per each booking and I have multiple bookings for each date. If I want the total available hours for each date how can I proceed?

Now my Date table and bookings table are connected with the date column do I need to create a new relation for connecting both of them with weekdays.

With the model that I have right now with the dates if I select  2023-Dec-19 since the weekday is tuesday I need to get only one value from resource_availability but since connected with dates I am getting the entire summarized week value instead of 450 I am getting 2250 for each resource available on the selected date as shown below. 

Can I have a measure to calculate available hours for the selected period (one date month or year) instead of the calculated column on the bookings level?

Please let me know if you understand my problem. Otherwise, I can explain in more detail.

[123abc]

Certainly, I understand the issue now. It seems that the relationship between the Date table and the Bookings table is causing the total available hours to be aggregated over the entire week rather than being specific to the selected date. To address this, you can create a new relationship between the Date table and the Bookings table based on the weekday.

Here are the steps you can take:

1. Create a Weekday column in the Date Table: If you don't have a weekday column in your Date table, create one. You can use the following DAX formula:

Measure:

Weekday = WEEKDAY(DateTable[Date], 2)

1. This formula assumes that your weekdays start from Monday (ISO week numbering).

2. Create a Relationship between Date Table and Bookings Table: Create a new relationship between the Date table and the Bookings table using the Weekday column in both tables.

3. Modify the TotalMinutesAvailable Measure: Adjust the TotalMinutesAvailable measure to consider the new relationship and calculate the available minutes for each selected date.

Measure:

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

1. In this formula, the USERELATIONSHIP function is used to specify the relationship between the Date column in the Bookings table and the Date column in the Date table based on the Weekday.

Now, when you select a specific date, the TotalMinutesAvailable measure should consider only the related weekday and provide the correct available minutes for that date.

Remember to adjust column and table names based on your actual model. If you encounter any issues or need further clarification, feel free to ask!

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.

[anusha_2023]

Thank you very much for your replay.  I do have couple of questions.

First One is that Create a Relationship between Date Table and Bookings Table: This relation is many to many with week numbers between Date table and bookings table right?

Second I am getting the following error while trying to write the measure. Could you please let me know what is the reason behind it.

[123abc]

Let's address your questions one by one:

1. Relationship Between Date Table and Bookings Table:

The relationship between the Date table and the Bookings table typically isn't a many-to-many relationship based on week numbers. Instead, it's typically a one-to-many relationship where each date in the Date table can have multiple bookings in the Bookings table.

However, if you want to consider week numbers for some calculations, you can create an additional column in both tables that represents the week number, and then establish a relationship based on that. But for the context of your previous questions, a direct relationship between the Date table and the Bookings table based on the date column should suffice.

2. Error Message:

You mentioned that you're encountering an error while trying to write the measure. To provide guidance on resolving the error, I'd need to see the specific error message you're receiving.

Please provide the exact error message or describe the issue you're facing in more detail. This will help me understand the problem better and offer a solution or workaround accordingly.

[123abc]

can you share data sample (pbix file) file please 

[123abc]

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.