Business Days Age calculation between two dates excluding weekends

[Anonymous]

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

[aj1973]

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]

Hi Amine,

If you can provide the DAX function to calculate the Age excluding weekends it would be helpful.

i am new to Power BI and not aware of DAX functions.

Regards,

Kiran

[aj1973 · Accepted solution]

@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]

                     )

            )

)