Sum of workdays for all employees over time fails due to changing number of employees.

[ABech]

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.

[Greg_Deckler]

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

[ABech]

Hello @Greg_Deckler 

Sure. I will try. Would you advice me to update the original post with more information or just add in here in the comments section? 

[Greg_Deckler]

@ABech Either way works.

[ABech]

Hi @Greg_Deckler . I've tried adding some more information and a sample now. 

Somehow it wont allow me to add borders to the tables. I get an HTML- error everytime i try to add borders.

I hope you can help me, since my deadline is coming up fast 😞

[Greg_Deckler · Accepted solution]

@ABech Try this:

Measure = 
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                SUMMARIZE('TableX',[YearMonth],"__Count",COUNTROWS(DISTINCT(SELECTCOLUMNS('TableX',"__Emploee",[*Employee ID*]))),"__Year",YEAR(MAX('TableX'[*Date*])),"__Month",MONTH(MAX('TableX'[*Date*]))),
                "__WorkingDays",COUNTROWS(FILTER('Date',YEAR([Date])=[__Year]&&MONTH([Date])=[__Month]&&[Working day]=1))
            ),
            "__TotalDays",[__Count]*[__WorkingDays]
        )
RETURN
    SUMX(__Table,[__TotalDays])

You will need a YearMonth column, that formula is this:

YearMonth = YEAR([*Date*])*100 + MONTH([*Date*])

[ABech]

Holy smokes! It worked! 

Thank you so much @Greg_Deckler. I honestly have no idea right now of how it worked, but it does. Again - thank you for your time and help.