Available Hours based on Start and End Dates

[rush]

Hi Everyone

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.

[v-lili6-msft]

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/38490

Best Regards,

Lin

[rush]

@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.

[v-lili6-msft]

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

[rush]

@v-lili6-msft @Stachu 

Sure, no problem.

The calculation should sum up all the hours from the Date Table column (

Available Working Days) based on their Start & End Dates.

If their Start & End Dates are not in the same period as the billing date
then it would just sum up their hours for that year-month.

Example 1:
Staff Name: CB
Start Date: 2019/07/15
Hours: 2019/07/15 - 2019/07/31 = 104 hours

The calculation should sum up all the hours from the Date Table between the dates (Start Date - End of the month for the Start Date).


Example 2:
Staff Name: CB
End Date: 2019/07/19
Hours: 2019/07/01 - 2019/07/19 = 120 hours

The calculation should sum up all the hours from the Date Table between the dates (Beginning of the month for  relevant the End Date - End Date)

[v-lili6-msft]

hi, @rush 

You could refer to this post:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

For this case, Do not create the relationship between data table and dim date table.

Best Regards,

Lin

[rush]

@v-lili6-msft @Stachu
@Phil_Seamark 

I cannot get it to work.

Please assist.

Thanks.

 

[v-lili6-msft]

hi, @rush 

Above post has the same logic of your case.

One question: Why do you only want to calculate the value in July 2019?

For CB, He has 5 rows data and start from 7/15/2019 and no end date.

and for FIONA,  he has multiple rows of data and start from 9/1/2011 and end at 7/19/2019.

What is your expected output?

only like this 

Name      Output
CB           104

FIONA     120

Best Regards,

Lin

[rush]

@v-lili6-msft @Stachu @Sean 

The output should be like the image below but adjusted for Start & End Dates:

For July 2019 for CB, it should calculate 104 hours as he started on the 15 July 2019.

[v-lili6-msft]

hi, @rush 

For your case, If you could try this simple way:

Create the relationship between dim date table and Billing Data by YYYYMM column.

Then use this formula to create a measure

Measure = 
CALCULATE (
    SUM ( Dim_Date[Available Hours] ),
    FILTER (
        Dim_Date,
        Dim_Date[Date] >= SELECTEDVALUE ( 'Billing Data'[Start Date] )
            && Dim_Date[Date]
                <= IF (
                    ISBLANK ( SELECTEDVALUE ( 'Billing Data'[End Date] ) ),
                    MAX(Dim_Date[Date]),
                    SELECTEDVALUE ( 'Billing Data'[End Date] )
                )
    )
)

Result:

here is pbix file, please try it.

Best Regards,

Lin

[rush]

@v-lili6-msft  Thank you as it is almost correct besides the fact that there should not be any blanks in the measure.

It would sum the available hours as normal for the previous months.

I added the Start & End Dates for reference purposes.

If it is possible that the total at the end should sum up based on the values displayed and not 91560.

[v-lili6-msft]

hi, @rush 

This looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

For your case, just add a new formula:

Measure 2 = var _table=SUMMARIZE('Billing Data','Billing Data'[Staff Name],'Billing Data'[YYYYMM],'Billing Data'[Start Date],'Billing Data'[End Date],"_value",[Measure]) return
SUMX(_table,[_value])

Result:

Best Regards,

Lin

[rush]

@v-lili6-msft 

Thanks for the helpful links and the new measure.

Any luck with regards to the original issue where there should not be any blanks in the measure as it is not calculating for the available hours for the precending months and brings back a blank?

Your help is much appreciated.

[v-lili6-msft]

hi, @rush 

CB is start from 7/19/2019, why months before July 2019 has value of it? what is the logic of this months?

Best Regards,

Lin

[rush]

@v-lili6-msft 

I need the available hours for each staff per year and month to calculate the utilization.

I checked now and I cannot use that measure even in a summary table as it causes my memory to increase to the point it freezes.

I have updated the PBIX file that contains list of all staff summarized by Year-Month whereby I would need that measure as a column which I have tried but it does not adjust for the Start and End Dates.

Sorry for these changes.

Thank you.

[Stachu]

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