Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 DateReporting DateBusiness Days Age
6/1/20216/7/20215
5/13/20216/7/202118
6/2/20216/7/20214
6/2/20216/7/20214
6/3/20216/7/20213
6/2/20216/7/20214
5/28/20216/7/20217
6/3/20216/7/20213
6/3/20216/7/20213
6/3/20216/7/20213
6/3/20216/7/20213
6/4/20216/7/20212
6/4/20216/7/20212
6/4/20216/7/20212
6/4/20216/7/20212
6/4/20216/7/20212
6/4/20216/7/20212
6/4/20216/7/20212
6/2/20216/7/20214
5/28/20216/7/20217
6/3/20216/7/20213
6/2/20216/7/20214
5/31/20216/7/20216
5/31/20216/7/20216
6/2/20216/7/20214
6/2/20216/7/20214
6/2/20216/7/20214
6/4/20216/7/20212
6/2/20216/7/20214
6/2/20216/7/20214
6/2/20216/7/20214
6/2/20216/7/20214
Tips and Tricks
  • aj1973's avatar
    aj1973
    5 years ago

    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

  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        aj1973
        Icon for Community Champion rankCommunity Champion
        5 years ago

        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]

                             )

                    )

        )