calculate average duration of users were active in selected period

[Ritaf]

Hi, 
I have a report of employees' attendance in the office.
To calculate the percent of working days that they attended correctly, I need to calculate how many working days they have in potential. So I have to require a calendar of working days and their hire date and their end date.
The formula I used is working perfectly for an employee granularity and incorrect for averages of all organization levels/managers/divisions etc... 
I will thank if somebody can help me to fix the last part of the formula to get the correct average.

I also put the PBIX with an example here:
https://drive.google.com/file/d/1UU-u3Z0xjU0MqPrC_RoRLhyvI1ksmYrC/view

WorkDaysForEmployee =

var start_day = if(max('employeesTable'[StartEmpoyeeDate])>min('Calendar'[Date]),max('employeesTable'[StartEmpoyeeDate]),min('Calendar'[Date]))

var end_day = if (ISBLANK( max('employeesTable'[LastEmpoyeeDate])),

max('Calendar'[Date]),if(MAX('employeesTable'[LastEmpoyeeDate]) > max('Calendar'[Date]),max('Calendar'[Date]),MAX('employeesTable'[LastEmpoyeeDate])))

var workdays = CALCULATE(COUNTROWS('Calendar'),'Calendar'[Date]>=start_day,'Calendar'[Date]<=end_day,'Calendar'[WorkdayFlag]=1)

var averageWorkdays = if (HASONEVALUE('employeesTable'[FullName]),workdays,AVERAGEX(VALUES('employeesTable'[FullName]),workdays))

return

averageWorkdays

[parry2k · Accepted solution]

@Ritaf I just tidy it up a bit and it is working, maybe tweak it further as you see fit

Workdays Final = 
VAR __min = MIN ( 'Calendar'[date] )
VAR __max = MAX ( 'Calendar'[date] )
RETURN
AVERAGEX (
    employeesTable,
    VAR start_day = MAX ( 'employeesTable'[StartEmpoyeeDate], __min ) 
    //if('employeesTable'[StartEmpoyeeDate]>__min,'employeesTable'[StartEmpoyeeDate],__min)
    VAR end_day = COALESCE ( MIN ( 'employeesTable'[LastEmpoyeeDate], __max ), __max )
     //if (ISBLANK( 'employeesTable'[LastEmpoyeeDate]), __max,if('employeesTable'[LastEmpoyeeDate] > __max,__max,'employeesTable'[LastEmpoyeeDate]))
    VAR workdays = 
        CALCULATE ( 
            COUNTROWS ('Calendar' ),
            'Calendar'[Date] >= start_day,
            'Calendar'[Date] <= end_day,
            'Calendar'[WorkdayFlag] = 1
        )
    RETURN
    workdays
)

. You can  remove those commented lines once you are happy with the result

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k]

@Ritaf this is what I see, there are 3 files, if you just give me the file name, it will be easier. 😁

[Ritaf]

sorry it's named SampleFile.pbix
maybe this link will take you to the right place

https://drive.google.com/file/d/1UU-u3Z0xjU0MqPrC_RoRLhyvI1ksmYrC/view?usp=sharing

[parry2k]

@Ritaf there are few files which one to use?

[Ritaf]

if you press download it takes only one PBIX.
I really don't know why it showing multiple files

[parry2k]

@Ritaf hmmm, can you share a sample pbix file, remove sensitive information before sharing.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Ritaf]

Hi,
thanks for responding 
I put a PBIX with an example here

https://drive.google.com/drive/folders/180w1__xEBq31goTDTM_gOOK_Y7MUzHkq?usp=sharing