Forum Discussion
Available Hours based on Start and End Dates
Hi Everyone :smileyhappy:
I am trying to calculate available hours for the month for each employee where their available hours would be adjusted based off their Start and End Dates:
Example: Start Date: 15 July 2019 = his/her available hours for that month only would be 104 based off a column in my Date table that calculates the available hours for each day.
Example End Date: 22 July 2019 = Summing available hours from the start of the End Date till the end of the End Date.
I tried to create a measure but does not return the correct result:
Available Hours = VAR Start_Date = MAX ( Staff[Date Employed] ) VAR End_Date = MAX ( Staff[Staff_Termination_Date] ) VAR Start_Date_YM = FORMAT ( MAX( Staff[Date Employed]) , "YYYYMM") VAR End_Date_YM = FORMAT( MAX(Staff[Staff_Termination_Date]) , "YYYYMM") VAR Avail_Hours = CALCULATE( SUM(Dim_Date[Available Hours]) , Dim_Date[YYYYMM] ) VAR Dim_Date_YM = FORMAT(MAX(Dim_Date[Calendar_Date]) , "YYYYMM") VAR Result1 = SWITCH( TRUE() , Start_Date_YM = Dim_Date_YM , CALCULATE ( Avail_Hours , DATESBETWEEN ( Staff[Date Employed] , MAX( Staff[Date Employed] ) , EOMONTH ( Start_Date , 0) ) ) , End_Date_YM = Dim_Date_YM , CALCULATE ( Avail_Hours , DATESBETWEEN ( Staff[Staff_Termination_Date] , STARTOFMONTH ( Staff[Staff_Termination_Date] ) , MAX( Staff[Staff_Termination_Date] ) ) ) , Avail_Hours ) RETURN Result1
Your help is much appreciated.
15 Replies
- Stachu
Community Champion
Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).
Column1 Column2 A 1 B 2.5 specifically Staff and Dim_Date
- v-lili6-msft
Community Support
hi, rush
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Best Regards,
Lin
- rush
Helper V
v-lili6-msft Stachu
Hi Everyone
Please see link to test data in the PBIX file:I have added 2 employees where one has an end date near the end of the month while the other has a start date in the middle of the month.
The available hours should not be duplicated in the billing table, once per month per employee.
Your help is much appreciated.
Thank you.:smileyhappy:- v-lili6-msft
Community Support
hi, rush
What is your expected output for these two employees?
Could you explain it according to your logic?
And I think you need to create a measure instead of calculate column for [Available Hours].
Best Regards,
Lin