Count Aggregate value of Employees from Working Days considering Date of Joining and Leaving

[HassanAshas]

Hi, I have a dataset that looks like this, 

Date	Emp Code	Name	Allocation	Project Code	DOJ	DOL
1/1/2023	1	Matt	0.4	Bench	1/1/2022
1/1/2023	1	Matt	0.2	A	1/1/2022
1/1/2023	1	Matt	0.3	C	1/1/2022
1/1/2023	2	Avery	0.6	A	2/14/2023
1/1/2023	3	Aryub	1	C	2/21/2023
1/1/2023	4	Jacob	0.1	Bench	3/21/2020
1/1/2023	4	Jacob	0.1	C	3/21/2020
1/1/2023	4	Jacob	0.4	A	3/21/2020
1/1/2023	5	Zara	0.6	Bench	5/11/2011	1/15/2023
1/1/2023	5	Zara	0.4	C	5/11/2011	1/15/2023
2/1/2023	1	Matt	0.4	Bench	1/1/2022
2/1/2023	1	Matt	0.2	A	1/1/2022
2/1/2023	2	Avery	0.6	Bench	2/14/2023
2/1/2023	2	Avery	0.3	A	2/14/2023
2/1/2023	3	Aryub	1	C	2/21/2023
2/1/2023	4	Jacob	0.5	Bench	3/21/2020
2/1/2023	4	Jacob	0.4	A	3/21/2020
2/1/2023	5	Zara	0	Bench	5/11/2011	1/15/2023
2/1/2023	5	Zara	0	C	5/11/2011	1/15/2023

Since the website disrupts the structure of table (I don't know why), I am also sharing a picture, for better understanding of the table values 

I want to count the Aggregate value of Employees based upon the Date of Joining and Date of Leaving for all the employees. So, for example, if a person has worked whole month (then his working days would be equal to total working days of month, therefore his aggregate value will be equal to 1). Please note that Project Code and Allocation doesn't matter in this calculation, however I am getting multiple rows per employee per month so that's why I kept them in dataset. 

If a person has worked half-month and left mid-way, then his aggregate value should be equal to his_total_working_days / total_working_days (that is, from the start of month to his Date Of Leaving, if he's worked 11 days and total working days are 22 then his aggregate value will be 0.5) 

Similarly, if a person joined late, then again, his aggregate value should be equal to his_total_working_days / total_working_days (and his working days will start from the day he joined) 

These values will all be added up at the end (that can be done using SUMX) to find aggregate value for all the employees, but I am unable to make the logic to incorporate Date of Joining and Date of Leaving. I tried to come up with the DAX as below, but it did not work for me. 

Aggregate Employees Value = 

var max_date = EOMONTH(MAX(Competency[Date]), 0) // To get the Max Value of Current Filter Context
var min_date = MIN(Competency[Date])

var month_working_days = NETWORKDAYS(MIN(Competency[Date]), EOMONTH(MAX(Competency[Date]), 0))
var aggregate_value = 
    SUMX(
        VALUES(Competency[Emp Code]), 
        var max_date = IF(
            MAX(Competency[DOL]) < max_date, // If leaving date is before max_date
            max_date = MAX(Competency[DOL]), 
            max_date
        ) 
        var min_date = IF(
            MAX(Competency[DOJ]) > min_date, 
            MAX(Competency[DOJ]), 
            min_date
        )

        var employee_working_days = NETWORKDAYS(min_date, max_date) 

        return employee_working_days / month_working_days
    )

    return aggregate_value

Can anyone help me out in this? Thanks a lot 

If you would like to download Power BI File with the given data, you may do so from here: https://drive.google.com/file/d/189HST29jdPHl7wFtWg0ZxK7TjHfniohy/view?usp=sharing

[tamerj1]

Hi @HassanAshas 
Please refer to attached sample file with the proposed solution

Aggregate Employees Value = 
VAR max_date = 
    CALCULATE ( 
        MAX ( 'Calendar'[Date] ), 
        REMOVEFILTERS ( ), 
        VALUES ( 'Calendar'[Month-Year] ) 
    ) -- to get the end of the max month within the selected period. Otherwize, use only MAX ( 'Calendar'[Date] )
VAR min_date = MIN ( 'Calendar'[Date] )
VAR month_working_days = NETWORKDAYS ( min_date, max_date )
VAR aggregate_value = 
    SUMX (
        VALUES ( Competency[Emp Code] ), 
        DIVIDE (
            CALCULATE ( DISTINCTCOUNT ( Competency[Date] ) ),
            month_working_days 
        )
    )
RETURN 
    aggregate_value

[HassanAshas]

Hi, thank you so much for replying. It kind of doesn't calculate the right number, however. 

I managed to compute the value of Aggregate for each employee using calculated column, let me share my Results for both months.

For the Month of January, 

For the month of Feb 

The measure I am trying to achieve is actually the SUM of these aggregate values. So, for example, in the Month of January

Emp 1: Matt
DOJ: 1 Jan, 2022
DOL: Blank
Therefore, his aggregate value is 1 (because he joined before Jan 23 and hasn't yet left the company) 

Emp 2: Avery 
DOJ: Feb 14, 2023
Aggregate Value: 0 (because he joined after the month of January) 

Emp 3: Aryub 
DOJ: Feb 21, 2023
Same case as above, aggregate value: 0

Emp 4: Jacob
DOJ: 21 March, 2020
DOL: Blank
Aggregate value: 1

Emp 5: Zara
DOJ: 11 May 2011
DOL: 15 Jan 2023
Aggregate value: 0.45 (because he left midway in the month) 

and hence, the result I am trying to get for January is 1 + 1 + 0.45 = 2.45, using a measure. 

This is how I have for now calculated Working Days for an employee using calculated column, 

Worked on Days = 
    var working_days = -1
    var last_month_date = EOMONTH(Competency[Date], 0)
    var min_date = 
        IF(
            Competency[Date] > Competency[DOJ], // if Date is larger than DOJ, min date should be equal to Date
            Competency[Date],
            // if Date is not larger than DOJ, then check if DOJ is larger than "last day" of current Date
            // If it is, then employee didn't work this month because he joined in some next month 
            // so just make min_date = -1 -> so as to return 0 at the end.
            IF(
                Competency[DOJ] > EOMONTH(Competency[Date], 0), 
                -1,
                Competency[DOJ])
        )
    var max_date =
        IF(
            ISBLANK(Competency[DOL]), 
            EOMONTH(Competency[Date], 0), 
        // if DOL is not blank, so person has either left in the same month, or in a previous 
        // in case of previous month(s), so make max_date = -1.  
            IF(
                Competency[DOL] < Competency[Date], 
                -1, 
                // if DOL is not less than Date, then this means person has left in the same month. So max date = DOL Date
                Competency[DOL]
            )
        )

    return IF(min_date = -1 || max_date = -1, 0, NETWORKDAYS(min_date, max_date))
    // return IF(working_days = -1, NETWORKDAYS(min_date, max_date), working_days) 

If anyone would like to take a look at it, I have shared file below. Thank you. 
Regardless, still grateful of you for taking time out and in replying to the problem. 

I have shared the updated Power BI File as below, https://drive.google.com/file/d/1uMwckCNSifKs0pOfD03sZgn-TyX8Xjn2/view?usp=sharing

[tamerj1 · Accepted solution]

@HassanAshas 
Hope this is what you're looking for

Aggregate Employees Value = 
VAR max_date = 
    CALCULATE ( 
        MAX ( 'Calendar'[Date] ), 
        REMOVEFILTERS ( ), 
        VALUES ( 'Calendar'[Month-Year] ) 
    ) 
VAR min_date = MIN ( 'Calendar'[Date] )
VAR month_working_days = NETWORKDAYS ( min_date, max_date )
VAR T = DISTINCT ( SELECTCOLUMNS ( Competency, "@EmpCode", [Emp Code], "@Date", [Date], "@DOJ", [DOJ], "@DOL", [DOL] ) )
VAR aggregate_value = 
    SUMX (
        T, 
        DIVIDE (
            NETWORKDAYS ( min_date, MIN ( COALESCE ( [@DOL], max_date ), max_date ) ),
            month_working_days 
        )
    )
RETURN 
    aggregate_value

[HassanAshas]

Hi, Thank you once again! This DAX almost solved the issue. It did however help me to get to my final answer, thank you! 

The problem coming with this DAX was this that it wasn't working well for some specific conditions, i.e, it was not taking in consideration the people who joined later than the MAX date of the current filter context. For example, in the following picture, aggregate value for Emp "Avery" and "Aryub" should be "0" but it is instead 1

Similarly, if somebody has left in a month prior to the current filter context (like Zara below left in January and current filter context is Feb), so again values were a bit wrong there.

So, I kind of modified the DAX and ended up with the following DAX, that gave me the correct. It's definitely not the best DAX in terms of optimization and syntax, and I clearly have no idea how can I improve it, haha, but it does solve the issue. 

Aggregate Employees Value = 
VAR max_date = 
    CALCULATE ( 
        MAX ( 'Calendar'[Date] ), 
        REMOVEFILTERS ( ), 
        VALUES ( 'Calendar'[Month-Year] ) 
    ) 
VAR min_date = MIN ( 'Calendar'[Date] )
VAR month_working_days = NETWORKDAYS ( min_date, max_date )
VAR T = DISTINCT ( SELECTCOLUMNS ( Competency, "@EmpCode", [Emp Code], "@Date", [Date], "@DOJ", [DOJ], "@DOL", [DOL] ) )
VAR aggregate_value = 
    SUMX (
        T, 
        DIVIDE (
            IF(
                COALESCE([@DOJ], max_date) > max_date,  // If DOJ is greater than max_date, so person has worked 0 hours. 
                0, 
                IF(
                    COALESCE([@DOL], min_date) < min_date, // if DOL is greater than min_date then person has worked 0 hours
                    0, 
                    NETWORKDAYS ( 
                        IF(
                            COALESCE([@DOJ], min_date) > min_date, // if DOJ is greater then min_date, then min_date should be DOJ
                            [@DOJ],
                            min_date
                        ),

                        IF(
                            COALESCE([@DOL], max_date) < max_date, 
                            [@DOL], 
                            max_date
                        )
                    ) 
                )
            ),
            month_working_days 
        )
    )
RETURN 
    aggregate_value

Now, I got the correct results for both the cases,

Again, I am very grateful for you replying and directing me to the right direction, which helped solve the problem for me eventually. Thank you so much!