Forum Discussion
Business Days Age calculation between two dates excluding weekends
Dear Team,
i am trying to create a column Business Days Age in power BI (Difference between two dates excluding weekends). below is the table i am referring to.
Note: "Reporting Date" column is created by Formula. finding difficulty in arriving "Network days"
tried creating a query as below, but it didn't work as the "Reporting Date" column not appearing in the formula bar. As it is not in the original Data table(it is created by a formula)
= (StartDate as date, EndDate as date) as number =>
let
ListDates = List.Dates(StartDate, Number.From(EndDate-StartDate),#duration(1,0,0,0)),
RemoveWeekends = List.Select(ListDates, each Date.DayOfWeek(_, Day.Monday)< 5),
CountDays = List.Count(RemoveWeekends)
in
CountDays
| Value Date | Reporting Date | Business Days Age |
| 6/1/2021 | 6/7/2021 | 5 |
| 5/13/2021 | 6/7/2021 | 18 |
| 6/2/2021 | 6/7/2021 | 4 |
| 6/2/2021 | 6/7/2021 | 4 |
| 6/3/2021 | 6/7/2021 | 3 |
| 6/2/2021 | 6/7/2021 | 4 |
| 5/28/2021 | 6/7/2021 | 7 |
| 6/3/2021 | 6/7/2021 | 3 |
| 6/3/2021 | 6/7/2021 | 3 |
| 6/3/2021 | 6/7/2021 | 3 |
| 6/3/2021 | 6/7/2021 | 3 |
| 6/4/2021 | 6/7/2021 | 2 |
| 6/4/2021 | 6/7/2021 | 2 |
| 6/4/2021 | 6/7/2021 | 2 |
| 6/4/2021 | 6/7/2021 | 2 |
| 6/4/2021 | 6/7/2021 | 2 |
| 6/4/2021 | 6/7/2021 | 2 |
| 6/4/2021 | 6/7/2021 | 2 |
| 6/2/2021 | 6/7/2021 | 4 |
| 5/28/2021 | 6/7/2021 | 7 |
| 6/3/2021 | 6/7/2021 | 3 |
| 6/2/2021 | 6/7/2021 | 4 |
| 5/31/2021 | 6/7/2021 | 6 |
| 5/31/2021 | 6/7/2021 | 6 |
| 6/2/2021 | 6/7/2021 | 4 |
| 6/2/2021 | 6/7/2021 | 4 |
| 6/2/2021 | 6/7/2021 | 4 |
| 6/4/2021 | 6/7/2021 | 2 |
| 6/2/2021 | 6/7/2021 | 4 |
| 6/2/2021 | 6/7/2021 | 4 |
| 6/2/2021 | 6/7/2021 | 4 |
| 6/2/2021 | 6/7/2021 | 4 |
Anonymous
- Add a calendar table to your model, let's call it DimDate
- Add column to the DimDate, use this formula
WorkingDay_Mark =
VAR WeekDayNum =
WEEKDAY ( DimDate[Date] )
RETURN
IF ( WeekDayNum = 1 || WeekDayNum = 7 ,0,1)
- the formula will mark 1 for working days and 0 for weekends
- to your fact table add column and use this formula
Business Days Age =
COUNTROWS (
FILTER (
DimDate,
AND (
AND (
DimDate[Date].[Date] >= Orders[Value Date].[Date],
DimDate[Date].[Date] <= Orders[Reporting Date].[Date]
),
DimDate[WorkingDay_Mark]
)
)
)
3 Replies
Hi Anonymous
Formulas created in the model you can't see them or use them in Power Query.
Use DAX in the model to get the numbers you want.
Anonymous
- Add a calendar table to your model, let's call it DimDate
- Add column to the DimDate, use this formula
WorkingDay_Mark =
VAR WeekDayNum =
WEEKDAY ( DimDate[Date] )
RETURN
IF ( WeekDayNum = 1 || WeekDayNum = 7 ,0,1)
- the formula will mark 1 for working days and 0 for weekends
- to your fact table add column and use this formula
Business Days Age =
COUNTROWS (
FILTER (
DimDate,
AND (
AND (
DimDate[Date].[Date] >= Orders[Value Date].[Date],
DimDate[Date].[Date] <= Orders[Reporting Date].[Date]
),
DimDate[WorkingDay_Mark]
)
)
)