Help with measuring accrued holiday based on employee start dates

[FD5]

I have a dataset that has timesheet data which we report on.

I have been asked to calculate variance between actual chargeable hours vs budgeted but also take into account holiday taken vs accrued holiday.

I have this measure but doesn't quite work

Accrued Holiday =

VAR StaffID = SELECTEDVALUE(Time[Staff index])

-- Get the employee's start date

VAR EmployeeStartDate =

   LOOKUPVALUE(Users[Employee Start Date], Users[Staff index], StaffID)

-- Start of financial year based on slicer

VAR FYStartDate =

   DATE(

       IF(MONTH(MIN('BaseCalendar'[BaseDate])) >= 6,

           YEAR(MIN('BaseCalendar'[BaseDate])),

           YEAR(MIN('BaseCalendar'[BaseDate])) - 1

       ),

       6, 1

   )

-- End of selected reporting period

VAR FYEndDate = MAX('BaseCalendar'[BaseDate])

-- Prevent accrual if joined after reporting period

VAR EffectiveStartDate =

   IF(EmployeeStartDate > FYEndDate, BLANK(), MAX(EmployeeStartDate, FYStartDate))

-- Days worked in FY

VAR DaysWorked =

   IF(

       NOT ISBLANK(EffectiveStartDate),

       DATEDIFF(EffectiveStartDate, FYEndDate, DAY) + 1,

       0

   )

-- Total days in FY (June–May)

VAR DaysInFY = DATEDIFF(FYStartDate, DATE(YEAR(FYStartDate) + 1, 5, 31), DAY) + 1

-- 33 days x 7 hours = 231

VAR AnnualHolidayHours = 231

-- Final result

VAR Accrued =

   IF(

       DaysWorked > 0,

       ROUND(DIVIDE(DaysWorked, DaysInFY) * AnnualHolidayHours, 1),

       BLANK()

   )

RETURN

   Accrued

But it returns thousands like this:

Can anyone help please? Much appreciated!

Thank you

Figen

[Shahid12523]

Use working days instead of calendar days if holidays accrue that way.
Fix FYEndDate to always be May 31 of the next year.
Make sure EmployeeStartDate is a valid date.
Consider pulling AnnualHolidayHours from a table if it varies by employee.

[Greg_Deckler]

@FD5 Can you post sample data as text?

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, 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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[FD5]

@Greg_Deckler thank you for your prompt response. I have the following snippets from the data tables and the report I am building. Are these helpful?

Many thanks

[v-dineshya]

Hi @FD5 ,

Thank you for reaching out to the Microsoft Community Forum.

Please try below Measure to fix the issue.

Accrued Holiday =
VAR StaffID = SELECTEDVALUE(Users[Staff index])
VAR EmployeeStartDate =
LOOKUPVALUE(Users[Employee Start Date], Users[Staff index], StaffID)
VAR FYStartDate =
DATE(
IF(MONTH(MIN('BaseCalendar'[BaseDate])) >= 6,
YEAR(MIN('BaseCalendar'[BaseDate])),
YEAR(MIN('BaseCalendar'[BaseDate])) - 1
),
6, 1
)
VAR FYEndDate = MAX('BaseCalendar'[BaseDate])
VAR EffectiveStartDate =
IF(
EmployeeStartDate > FYEndDate,
BLANK(),
MAX(EmployeeStartDate, FYStartDate))

VAR DaysWorked =
IF(
NOT ISBLANK(EffectiveStartDate),
DATEDIFF(EffectiveStartDate, FYEndDate, DAY) + 1,
0
)

VAR DaysInFY = DATEDIFF(FYStartDate, DATE(YEAR(FYStartDate) + 1, 5, 31), DAY) + 1
VAR AnnualHolidayHours = 231

RETURN

IF(
DaysWorked > 0,
DIVIDE(DaysWorked, DaysInFY) * AnnualHolidayHours,
BLANK()
)

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[FD5]

@v-dineshya 

Hi Dinesh

Sorry for the delay. I applied your suggested DAX but the accrued holiday is still displaying rather large numbers. Please see below screenshot.

[v-dineshya]

Hi @FD5 ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please provide sample PBIX file based on the sample data you provided.

Regards,

Dinesh

[v-dineshya]

Hi @FD5 ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please provide sample PBIX file based on the sample data you provided.

Regards,

Dinesh

[FD5]

@v-dineshya thank you for your help. I am not sure about creating a sample data.

I think the issue is with the calendar table and the employee start date. 

I will try and resolve this myself, and if I can't I will try the sample data.

Thanks

[v-dineshya]

Hi @FD5 ,

Thank you for the update. As you mentioned in your previous response, you are trying to resolve the issue. Please provide ETA (Estimated Arrival time)  to resolve the issue.  Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @FD5 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[FD5]

@v-dineshya thanks for following up. I have asked our external databse consuktants to query this data straight from the system, but I am yet to review and agree that all working ok. If I have any more queries or would like to revisit the DAX query, I will let you know. Many thanks

[v-dineshya]

Hi @FD5 ,

Thank you for the update. Please do let us know if you have any further queries.

Regards,

Dinesh