topic Help with measuring accrued holiday based on employee start dates in DAX Commands and Tips

Help with measuring accrued holiday based on employee start dates

FD5 — Thu, 14 Aug 2025 16:55:03 GMT

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,

)

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

Re: Help with measuring accrued holiday based on employee start dates

Greg_Deckler — Thu, 14 Aug 2025 17:38:16 GMT

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.

Re: Help with measuring accrued holiday based on employee start dates

FD5 — Fri, 15 Aug 2025 09:44:46 GMT

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

Re: Help with measuring accrued holiday based on employee start dates

v-dineshya — Mon, 18 Aug 2025 13:23:40 GMT

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

Re: Help with measuring accrued holiday based on employee start dates

FD5 — Wed, 20 Aug 2025 16:05:00 GMT

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.

Re: Help with measuring accrued holiday based on employee start dates

Shahid12523 — Fri, 22 Aug 2025 03:46:28 GMT

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.

Re: Help with measuring accrued holiday based on employee start dates

v-dineshya — Fri, 22 Aug 2025 04:12:08 GMT

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

Re: Help with measuring accrued holiday based on employee start dates

v-dineshya — Mon, 25 Aug 2025 13:08:01 GMT

Hi @FD5 ,

Regards,

Dinesh

Re: Help with measuring accrued holiday based on employee start dates

FD5 — Tue, 26 Aug 2025 15:35:24 GMT

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

Re: Help with measuring accrued holiday based on employee start dates

v-dineshya — Thu, 28 Aug 2025 09:17:21 GMT

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

Re: Help with measuring accrued holiday based on employee start dates

v-dineshya — Tue, 02 Sep 2025 05:35:28 GMT

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

Re: Help with measuring accrued holiday based on employee start dates

FD5 — Thu, 04 Sep 2025 11:47:12 GMT

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

Re: Help with measuring accrued holiday based on employee start dates

v-dineshya — Thu, 04 Sep 2025 12:24:38 GMT

Hi FD5 ,

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

Regards,

Dinesh