Forum Discussion

rush's avatar
rush
Icon for Helper V rankHelper V
7 years ago

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's avatar
    Stachu
    Icon for Community Champion rankCommunity 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).

    Column1Column2
    A1
    B2.5

    specifically Staff and Dim_Date

    • rush's avatar
      rush
      Icon for Helper V rankHelper V

      v-lili6-msft Stachu 

      Hi Everyone

      Please see link to test data in the PBIX file:


      PBIX

       

      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's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity 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