Resource Utilization by month for available days

[Ed123456]

I'm unable to calculate the possible billable days per month by employee, taking into consideration holidays and resource start & end dates.  I received this error message "USERELATIONSHIP function can only be used in the CALCULATE function".  I've used it as a filter.  This is the variable I've created for months during which an employee started after the first of the month

VAR StartDuringMonth =

CALCULATE(

    COUNTROWS(

        FILTER(

            Calendar_LookUp,

            Calendar_LookUp[Date] >= USERELATIONSHIP(Calendar_LookUp[Date], Resources[Resource Start Date]) &&

            Calendar_LookUp[Date] <= Calendar_LookUp[End of Month] &&

            Calendar_LookUp[Date] <> RELATED(Holidays[Holidays]) &&

            Calendar_LookUp[Date] <> "Saturday" || "Sunday"

        )

    )

)

[Ed123456]

Thanks for your feedback however I'm unable to resolve this problem...  

I need to filter the data table (USA_TimeListing) by two date ranges, one from each lookup table.

The date on the calendar lookup table is to cover all calendar dates, which will show all dates when used as a matrix column header. 

The resource start and end dates, from the resource lookup table, is to be used to filter the dates, at the resource level, on the data table. 

My goal is to identify the number of billable days that the each resource could've worked, to be used as a denominator while calculating utilization. 

I want the matrix to display the possible billable days for each resource during each month, taking into consideration partial months during which resources may have started or ended. (ex: resource starts halfway through month - only half of the month will be considered)

[v-yueyunzh-msft]

Hi , @Ed123456 

According to your description, your need is to calcualte the Resources' working days between its' Start Date and End Date.

If this , here are the steps you can refer to :
(1)This is my test data: 

'Resources' and the 'Holidays' Table

And i use the dax code to create a ‘Calendar LookUp’ Table:

Calendar_LookUp = ADDCOLUMNS( CALENDAR( FIRSTDATE('Resources'[Resource Start Date]), LASTDATE('Resources'[Resource End Date])) ,"WeekDay", WEEKDAY([Date],2))

I think you need to put the 'Resources'[Resource] field on the visual as a dimension so that i think we do not need to create a relationship between the ‘Calendar LookUp’ and 'Resources' Table.

Then we can click "New Column" in 'Calendar LookUp' Table to create a working day flag column :

Flag = IF( RELATED('Holidays'[Holidays]) || [WeekDay] in {6,7} , -1 ,1)

Then we can create a measure like this:

Measure = var _start_date = MAX('Resources'[Resource Start Date])
var _end_date = MAX('Resources'[Resource End Date])
var _days = CALCULATE( SUM('Calendar_LookUp'[Flag]) , 'Calendar_LookUp'[Date]>= _start_date , 'Calendar_LookUp'[Date] <= _end_date , 'Calendar_LookUp'[Flag] =1)
return _days

Then we can put this measure on the visual and we can get each resources' woking days:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Ed123456]

This doesn't work with the data table... the resources table is to be used as a lookup table for the fact table.

[v-yueyunzh-msft]

Hi, @Ed123456 

I'm sorry that i may got missing for your need , can you explain the meaning of the "the resources table is to be used as a lookup table for the fact table"?Isn't the number of days calculated based on the start and end dates of Resources?

Can you provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Best Regards,

Aniya Zhang

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

[Ed123456]

I have two lookup tables which are both connected to a data table. 

-One is for all calendar dates, to be used as a matrix column header. 

-The other is for resources and their employment dates.  The resources are to be used as a matrix row header.

-The data table contains actual hours worked by resource by day. 

-The values is going to be a measure which calculates utilization % by resource & date for which they were employed.  This is particularly tricky when it comes to resources that only worked half of a month.  For example, if a resource started on the 15th of a month with 20 billable days, their possible utilization should be 80 hours rather than 160.

The overall goal is to create a matrix with all calendar months as the column headers, all resources as the row headers, and the utilization percentage as the values. 

The first variable that I need to calculate needs to show possible hours worked by resource and by month. 

Your example provided a way to calculate the possible billable hours for each resource during the period for which they are/were employed.

I was unable to use the possible billable hours field in the matrix because the resources table isn't connected to the data table. 

[v-yueyunzh-msft]

Hi , @Ed123456 

Thank you very much for your quick reply and patient explanation.

From your description, I can see that you use calendar dates and resources as dimensions in your visuals.

My understanding is that both your date table and your Resources table belong to dimension tables and are associated with your fact table.

I don't quite understand why there are two relationships between the date table and your Resoucres table, as both are dimension tables.

Secondly, without sample data, it is difficult to really understand your needs through language description, can you provide me with a little sample data for me to test?You can provide your sample data in table form and the results you ultimately want in the visual.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[v-yueyunzh-msft]

Hi , @Ed123456 

According to your dax code , i think the USERELATIONSHIP() used may be wrong .

If you use this function alone, you will not return a default value, for your judgment condition, you need to compare, then you need to return a scalar value after your ">=".

However, the USERELATIONSHIP() function  is generally used to temporarily change the calculation of the model relationship in the measure, and needs to be used with the CALCULATE() function.

For more information, you can refer to :
USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Ed123456]

Can you suggest an alternative formula?

[v-yueyunzh-msft]

Hi,  @Ed123456 

I am not surely understand your logic in the dax code due to it may put in different content filter.
But for this dax code,first you need to replace the USERELATIONSHIP(Calendar_LookUp[Date], Resources[Resource Start Date])
to the value you need to put here,like this：
CALCULATE(MAX(Resources[Resource Start Date]),USERELATIONSHIP(Calendar_LookUp[Date], Resources[Resource Start Date]))

You can try to update this part of Dax code in your logic to judge.

For more information, you can refer to :
Using USERELATIONSHIP in DAX - SQLBI

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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