Number of days in Month from a date range

[Chippy]

I am looking for a way to report in PowerBI leave/absences from work by employee and department by month, however I don't have daily breakdown but date range only, as per example, where for employee 1000675 (2nd row) has taken 16 days over two months. How can I work out how many working days (Mon to Fri) were taken for each of Dec and Jan?

Employee	Department	Leave Type	Start Date	End Date	Days Taken
1000706	Regional Sales	Maternity Leave Unpaid	18/03/24	24/01/25	218.00
1000675	Finance Leadership	Annual Leave	27/12/24	17/01/25	16.00
2302135	Regional Sales	Annual Leave	23/12/24	10/01/25	13.00
2303149	Regional Sales	Annual Leave	27/12/24	8/01/25	9.00
1000812	Convenience & Specialty	Annual Leave	20/12/24	7/01/25	11.00
1000275	P&P Leadership	Annual Leave	24/12/24	6/01/25	6.00
1000769	Operations Leadership	Annual Leave	23/12/24	3/01/25	8.00
2304494	Marketing Management	Annual Leave	23/12/24	3/01/25	8.00
1176278	Finance Leadership	Annual Leave	16/12/24	3/01/25	13.00
1000379	Retail	Annual Leave	24/12/24	1/01/25	5.00
1405149	Operations Leadership	Annual Leave	23/12/24	1/01/25	6.00
1000477	Regional Sales	Annual Leave	9/12/24	1/01/25	15.00
1000081	Finance Leadership	Annual Leave	31/12/24	31/12/24	0.45

[rajendraongole1 · Accepted solution]

Hi @Chippy - Hope in your data model Calendar Table that includes every date in your data range.

add a new column as like below:

IsWorkingDay = IF(WEEKDAY('Calendar'[Date],2) <= 5, 1, 0)

start creating a  measure to count the total working days for a leave period

TotalWorkingDays =
CALCULATE(
COUNTROWS('Calendar'),
'Calendar'[Date] >= MIN('LeaveData'[Start Date]) &&
'Calendar'[Date] <= MAX('LeaveData'[End Date]),
'Calendar'[IsWorkingDay] = 1
)

Next measure to split the working days by month

WorkingDaysInMonth =
CALCULATE(
COUNTROWS('Calendar'),
'Calendar'[Date] >= MAX(DATE(YEAR('Calendar'[Date]), MONTH('Calendar'[Date]), 1)) &&
'Calendar'[Date] <= MAX('LeaveData'[End Date]),
'Calendar'[IsWorkingDay] = 1
)

Using a visual or filter in Power BI, you can now filter the leave records by month and calculate how many working days were taken in each of those months.

Hope this helps.

[dk_dk · Accepted solution]

Hi @Chippy 

Based on this blog post from @amitchandak :

In Power Query, you add a custom column as per the blog post and expand to new rows.

You can add another custom column to get the day of the week using Date.DayOfWeekName().

Here is the whole M query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZLRTsIwFIZfZdk1Yee0XbtdGhOvJBKJV8hFAyfYOMsyCglvb8ciqzrKjL1quub7T7/9y2WKAKBAppP0mbZmZ3WVLHRFe38w044aa9wpeSR9pOTF1tps/AcsMuAZE37LRAaYsdxvk2AxLKYA6WrSBUjVXngwVts1tbQNNfs3U/vDO2sPPvOc0PJUhqxDoxpE+4XyC844MOT50PQ/wbwHw1UwD8AcRTkG3E9cXOP6VYY+CmT+0v3OHskaap28HgCYTBY1rY2u3GkgBy4518VgmMLO1ucdeh7XLi5wGXmEDPFKtnqeamq084b28YBeP48EFIF/IUpxbmHzTs7YbTLTVm/pg6z7Px9RSaaKka1EeZved6eVw1XXHadNFdWNkWnzC1FA3rXx77pjAd/+p1BqRN3L22DMQ6wv+0jLHHvL/fb31DAVebpafQI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Department = _t, #"Leave Type" = _t, #"Start Date" = _t, #"End Date" = _t, #"Days Taken" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", Int64.Type}, {"Department", type text}, {"Leave Type", type text}, {"Start Date", type date}, {"End Date", type date}, {"Days Taken", Int64.Type}}),
    #"Divided Column" = Table.TransformColumns(#"Changed Type", {{"Days Taken", each _ / 100, type number}}),
    #"Added Custom" = Table.AddColumn(#"Divided Column", "Custom", each List.Dates([Start Date], Duration.Days([End Date] -[Start Date]) +1 ,#duration(1,0,0,0) )),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Days"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Days", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Day Name", each Date.DayOfWeekName([Days]))
in
    #"Added Custom1"

You will need to add some additional data about which days are red days / holidays. I noted that your Days Taken column excludes these from the total, even if they are on a weekday. Depending on the country, the red days are different so I did not include a step for this.

You can close and load.

Create a new measure:

Number of Weekdays = CALCULATE(COUNTROWS(LeaveData),LeaveData[Day Name] IN {"Monday","Tuesday","Wednesday","Thursday","Friday"})

You need to modify this measure so it filters out the red days/holidays as well.

And the result:

I hope this helps! Let me know if you have any questions.

[rajendraongole1 · Accepted solution]

Hi @Chippy - Hope in your data model Calendar Table that includes every date in your data range.

add a new column as like below:

IsWorkingDay = IF(WEEKDAY('Calendar'[Date],2) <= 5, 1, 0)

start creating a  measure to count the total working days for a leave period

TotalWorkingDays =
CALCULATE(
COUNTROWS('Calendar'),
'Calendar'[Date] >= MIN('LeaveData'[Start Date]) &&
'Calendar'[Date] <= MAX('LeaveData'[End Date]),
'Calendar'[IsWorkingDay] = 1
)

Next measure to split the working days by month

WorkingDaysInMonth =
CALCULATE(
COUNTROWS('Calendar'),
'Calendar'[Date] >= MAX(DATE(YEAR('Calendar'[Date]), MONTH('Calendar'[Date]), 1)) &&
'Calendar'[Date] <= MAX('LeaveData'[End Date]),
'Calendar'[IsWorkingDay] = 1
)

Using a visual or filter in Power BI, you can now filter the leave records by month and calculate how many working days were taken in each of those months.

Hope this helps.