Forum Discussion
Helper IVCalculate available time from resource table, resource_availability dim and bookings fact tables
Helper IV
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
Community Champion
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:
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)
This formula assumes that your weekdays start from Monday (ISO week numbering).
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.
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])
)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
can you share data sample (pbix file) file please
