DAX for percentage attendance in powerbi

[MEHUL123]

Hi,

I have 2 tables,

1) Datetable (having date, months, years)

2) Employee roll wise and date wise attendance

I want to display %Attendance of each employee in output

i.e.  --  ideal work days (day count in excluding sunday on any specific month or combination of multiple months) vs actual worked days of any employee for that specific period

Let Say,

EMP_ID	TOTAL work days of specific months	Actual Worked Days of employee on those months	%Attendance
123	26	20	77%
345	26	21	81%
567	26	25	96%
789	26	27	104%
TOTAL	26	23.25	89%

However when i select multiple months (let say selection of Jan25 and Feb25 in filter), than i want sum of 2 month's work days,

means [26 work days (jan25)  + 24 work days (feb25)] = 50 work days total...

pls guide, thanks in advance.

[v-hashadapu]

Hi @MEHUL123 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @MEHUL123 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @MEHUL123 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[rohit1991 · Accepted solution]

Hi @MEHUL123 ,
To calculate % Attendance in Power BI based on your two tables — a DateTable and an AttendanceTable (employee attendance records), you’ll need to approach this by comparing the number of ideal working days (excluding Sundays) from the date table against the actual worked days from the attendance records.

The key is to make sure both tables are linked via date, and that your DAX logic adjusts dynamically when multiple months are selected. You can then use DAX measures to calculate total workdays, actual attendance, and finally the percentage. This approach will respect any filters (like month slicers) and provide correct calculations even when multiple months are selected.

Step 1: Measure for Total Working Days (excluding Sundays)

TotalWorkingDays =
CALCULATE(
    COUNTROWS(DateTable),
    DateTable[DayName] <> "Sunday"
)

Step 2: Measure for Actual Worked Days (per employee)

ActualWorkedDays =
CALCULATE(
    COUNTROWS(AttendanceTable),
    AttendanceTable[Status] = "Present"  -- or whatever status indicates attendance
)

Step 3: Measure for % Attendance

% Attendance =
DIVIDE([ActualWorkedDays], [TotalWorkingDays], 0)

Make sure your DateTable is marked as a Date table and is properly related to the AttendanceTable on the date column. This setup will give you accurate, dynamic attendance percentages across any time period you slice by — including combinations of months, quarters, or years.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar

[AnkitKukreja]

Hi! @MEHUL123 

I think you are looking for this, if this doesn't help please do share a sample file.
https://www.youtube.com/watch?v=k0I-khGGXE4