How to calculate the number of days of absence per month

[Boeboey]

I have an absence table with the colums [absence start date] and [absence end date].
I would like to calculate the total number of days of absence per month

E.g.

absence 1 | start: 28-01-2025 | end: 03-02-2025 (4 days in January and 3 days in February)

absence 2 | start: 23-01-2025 | end: 26-01-2025 (4 days in January)

absence 3 | start: 01-02-2025 | end: 06-02-2025 (6 days in February)

Result:

January: 8 days

February: 9 days

Any idea how to create such a measure (or a calculated column)?

[Boeboey]

Thanks for the quick answer!!

[v-pgoloju]

Hi @Boeboey,

Just following up to check if the solution shared by our Super User helped resolve your issue. If you're still facing difficulties or need further assistance, please let us know — we’re here to help!

If the response addressed your query, we kindly request you to mark it as Accepted Solution and click Yes if you found it helpful. This supports others in the community as well.

Best regards,

Prasanna Kumar

[bhanu_gautam · Accepted solution]

@Boeboey 

Create a Date table if you don't already have one. This table should include all the dates for the period you are analyzing.

Create a measure to calculate the total number of days of absence per month.

DAX
TotalAbsenceDays =
VAR StartDate = MIN('AbsenceTable'[absence start date])
VAR EndDate = MAX('AbsenceTable'[absence end date])
VAR DateRange =
ADDCOLUMNS(
CALENDAR(StartDate, EndDate),
"YearMonth", FORMAT([Date], "YYYY-MM")
)
RETURN
SUMX(
DateRange,
VAR CurrentYearMonth = [YearMonth]
VAR AbsenceDays =
CALCULATE(
COUNTROWS('AbsenceTable'),
FILTER(
'AbsenceTable',
'AbsenceTable'[absence start date] <= [Date] &&
'AbsenceTable'[absence end date] >= [Date]
)
)
RETURN AbsenceDays
)

[rosha_rosha]

Hi,

1. Create a Dates Table (if you don’t already have one)

Dates =
ADDCOLUMNS (
CALENDAR ( MIN(Absences[absence start date]), MAX(Absences[absence end date]) ),
"Year", YEAR([Date]),
"Month", FORMAT([Date], "MMMM"),
"Month Year", FORMAT([Date], "MMM YYYY")
)

2. Create a Bridge Table Between Dates and Absences

You need a many-to-many relationship, so we create a virtual table (via a measure) or a flattened version via Power Query or DAX.

But here's a dynamic DAX approach using a measure:

Days of Absence =
CALCULATE (
COUNTROWS (
FILTER (
ADDCOLUMNS (
VALUES ( Dates[Date] ),
"IsAbsent",
CALCULATE (
COUNTROWS ( Absences ),
FILTER (
Absences,
Dates[Date] >= Absences[absence start date]
&& Dates[Date] <= Absences[absence end date]
)
)
),
[IsAbsent] > 0
)
)
)

Another Alternative way in Power Query:

Power Query Code:

let
// 1. Load your Absences table
Source = Absences,

// 2. Add a Custom Column that generates a list of dates from start to end
AddDates = Table.AddColumn(Source, "DateList", each List.Dates([absence start date], Duration.Days([absence end date] - [absence start date]) + 1, #duration(1,0,0,0))),

// 3. Expand the DateList into rows
ExpandedDates = Table.ExpandListColumn(AddDates, "DateList"),

// 4. Add Year, Month, and Month-Year columns for grouping
AddYear = Table.AddColumn(ExpandedDates, "Year", each Date.Year([DateList])),
AddMonth = Table.AddColumn(AddYear, "Month", each Date.Month([DateList])),
AddMonthName = Table.AddColumn(AddMonth, "Month Name", each Date.ToText([DateList], "MMM yyyy")),

// 5. Group by Month-Year and count rows (days)
Grouped = Table.Group(AddMonthName, {"Month Name"}, {{"Days of Absence", each Table.RowCount(_), Int64.Type}}),

// 6. Optional: Sort by date for better visuals
AddSortDate = Table.AddColumn(Grouped, "SortDate", each Date.FromText("01 " & [Month Name])),
Sorted = Table.Sort(AddSortDate, {{"SortDate", Order.Ascending}}),
Final = Table.RemoveColumns(Sorted, {"SortDate"})
in
Final