absence
1 Topic
Sum of workdays for all employees over time fails due to changing number of employees.
Hello Im pretty new in the dax world and need help on my new job. Thanks in advance. Calculating the time absent isn't the problem. I got that correctly. The problem is i have to calculate the %-ratio between abscence time and full contract time across all employees. I actually just need a formula saying something like: number of "employees * contract hours" over the whole given period. The problem is that the number of employees changes a lot over time, so when i try to make a ratio for the whole year (or longe period) the number gets way to big, because i used DISTINCTCOUNT for employee-ID, hence it just uses the total number of employees for the whole period although many employees weren't hired during some parts of the year. So far i calculated at chart showing both ebsent time (columns) and contract time (line) in days for every month, and that works well since we only hire the 1st day of every month and people mostly stops the last day of the month, so the number of employes for within a months rarely changes. The formula for the measure showing the contract hours in this chart is : Coontract time without holidays and vacation = CALCULATE( DISTINCTCOUNT ( TableX[EmployeeID] ) * CALCULATE ( COUNTA ( 'Date'[Day Type] ), FILTER ( 'Date', 'Date'[Day Type] = "Working day" ) ) - DIVIDE ( CALCULATE ( SUM ( 'TableX'[Quantity of hours registred] ), 'TableX'[HourCode] IN { "VacationCode" } ), 7.4 ) , ALL( 'TranslationOfHourCodeNames'[HourCode] ) ) I first fint the distinct number of employees and multiplicates it by the number of workdays, then i subtract vacation hours registred which is divided by 7,4 to get days insted of hours. The last part is just to make sure a slicer doenst ruin my chart. I use SQLBI's Date Table Template. therefore my date table is enriched with workdays, holidays etc. Again the problem is when i use a Card visual to show the ratio for the whole year/period, where i divide the total number of Abscent Days for all employees with this formula for Contract Days. For example we were 43 employed in august and 33 in january, but it multiplies by an even bigger number for the whole of 2021 because employees come and go, and everyone gets a unique employeeID. I dont know, if a solution could be, if it somehow could make the calculation month by month and add it all up for the given period? If i add up all numbers for my chart manually it gives the correct number. I hope you DAX geniuses can help me once more 🙂 EDIT: Greg_Deckler asked me to add more information and maybe a samlpe table. I've tried to add a small sample illustrating my problem calculating the contract hours over a several months period because of changing number of employees. I've tried to make it as simple as possible. I assume here, that every month contains 21 work days Small illustration of dataset: *Date* *Employee ID* *Quantity of hours registred* *HourCode* 01-01-2021 JIM 6 XXXXX 02-01-2021 JIM 7 VacationCode 03-01-2021 JACK 4 XXXXX 04-01-2021 JOE 6 XXXXX 01-02-2021 JIM 6 XXXXX 02-02-2021 JACK 7 XXXXX 01-03-2021 JIM 6 XXXXX 02-03-2021 JACK 8 XXXXX 03-03-2021 JAMES 5 XXXXX 04-03-2021 JACK 8 XXXXX Makes this calculation for everymonth in my chart based on the above table: January = 3 employees * 21 days = 63 days February = 2 employees * 21 days = 42 days March = 3 employees * 21 days = 63 days Total = 168 days But when i add my formula to a Card Visual showing the total work days in the period january-march, the formula does the the following (and wrong) calculation: January - march = 4 employees * 21 days * 3 months = 252 days The DISTINCTCOUNT formula just takes into account how many employees that was employed during the period, and doesnt take into account that some was only employed for a short while, and i dont know how to fix this. The part about subtracting holidays and the ALL-formula is so far i can see not of importance for this matter. Likewise is the part where i divide by 7,4 also not important, since that calculation is only to get from hours to days.Solved2.1KViews0likes6Comments