How to calculate working hours by employees

[PeterLabuda]

Hi everyone,

can anyone help me to find solution? Im new in DAX and i want to create correct formula, which will calculate all working hours by law per year.

I have (not me ) 60 employees (ID's), where some of them have classic (8-hours per day) contract and some have less - 4, 5 or 6 hours per day. Those with classic contract have (in 2020 f.e.) 251 days * 8 hours = 2008 working hours by law, and those with less hours contract have f.e. 251 days * 5 hours = 1255 hours by law. Plus, some of them started to work f.e. 1.7.2020 (Entry date), so they don't have full 251 days for work, but only 127 days and with 6 hours per day it is 762 work hours by law.

The "Entry date" and "Contract" columns are in query "Employees" (from Sharepoint List), BUT i think "EntryDate" may not be needed to solve for now because i already have formula for counting work days:

WorkDaysByLaw = IF(DateTable[IsHoliday]="No"  &&  DateTable[IsWeekend]="No", 1, 0)

/Holidays table - with dates of holidays in Slovakia - it's in a separate table and were connected to DateTable in Power Query/

I have matrix table in visual; in columns: TotalWorkHours (by law), RealWorkHours and Difference, and in rows are months from january to december (in format MMYYYY; in 2020: 202001 - 202012). TotalWorksHours in rows (by month) seems to be OK, but the total is incorrect - as i write below.

I just need to get total sum of all work hours by law per whole year and every employee together, something like:

TotalWorkHours = "User1(WorkDaysByLaw1 * Contract1) + User2(WorkDaysByLaw2 * Contract2) + ... + User60(WorkDaysByLaw60 * Contract60)" - this must be done for every employee and then "SUM" it

For now i have this but even if it calculate OK per month - in rows of matrix table numbers seems to be ok - but in total under the matrix table that sum is NOT correct:

TotalWorkHours = SUM(DateTable[WorkDaysByLaw]) * SUM(Employees[Contract])

and this formula works fine when i select just one ID (Employee) BUT when i select everyone in selected year, the result is: 251 days * 8 hours per day - and that's incorrect because every employee has his own contract (different number of hours per day) and some of them started to work later then 1.1.YYYY, so every employee has his own number of work days by law and his own number of hours per day (contract).

So please, can anybody help? 🙂 Probably with some "VAR's" or something? 🙂

THX

[Anonymous]

Hi @PeterLabuda ,

Could you please share some sample data and the expected result to have a clear understanding of your question? I can do some tests for you.

You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.

Best Regards,

Yuna

[MFelix]

Hi @PeterLabuda ,

For this you need to use an aggregation formula in this case SUMX, the X part of the formula means that it does the calculation line by line and then sum the values in the end.

In this case, and without specific dates is difficult to give correct formula, you need to use something similar to:

Total Work Hours = SUMX(DateTable,SUM(DateTable[WorkDaysByLaw]) * SUM(Employees[Contract]))

If this does not work can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.