Forum Discussion

bdehning's avatar
bdehning
Post Prodigy
2 years ago

Networkdays

I tried using the following but it is one day off due to February 29th this year I beleive.  I got total days of 106 but it should be 107?    WorkingDaysFriSatHolidays = VAR _holidays = {DATE(202...
Need Help
Show and Tell
Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

bdehning OK, did some testing. I believe the issue is that you specified 7 in the third parameter meaning that weekends are Friday and Saturday. 5/31/2024 falls on a Friday. See attached PBIX where I did some tests with 2/29/2024 and then Table2 shows an counter in Column 2 for all previous weekdays. It comes out to 109. So that minus 3 is 106

  • bdehning's avatar
    bdehning
    Post Prodigy
    2 years ago

    Greg,   How do I incorporate my Date of Service Filter

    Date of Service =
    VAR dte =
    DATE(
        INT(isiInspections[DateOfService] / 10000)
        , MOD(INT(isiInspections[DateOfService] / 100), 100)
        , MOD(isiInspections[DateOfService], 100)
    )
    RETURN
    IF(
        dte < TODAY()
        , dte
    )

     

    So it will control my Card or be a mesure that provides number of working days.

       

    Working Days =
    VAR _FirstDate = MIN('VW_DATE_DIM'[Date])
    Var _LastDate = MAX('VW_DATE_DIM'[Date])
    Var _Holidays =CALCULATETABLE(
            DISTINCT('VW_DATE_DIM'[DATE]),
            'VW_DATE_DIM'[ISHOLIDAY] = 1)

    RETURN

    NETWORKDAYS(
       _FirstDate,
       _LastDate,
        1,
        _Holidays)
     
     
    • bdehning's avatar
      bdehning
      Post Prodigy
      2 years ago

      I got close with, but I am off a few days so something is not counting right.  

      Working Days =
      VAR _FirstDate = MIN('isiInspections'[Date of Service])
      Var _LastDate = MAX('isiInspections'[Date of Service])
      Var _Holidays =CALCULATETABLE(
              DISTINCT('isiInspections'[Date of Service]),
              'VW_DATE_DIM'[ISHOLIDAY] = 1)

      RETURN

      NETWORKDAYS(
         _FirstDate,
         _LastDate,
          1,
          _Holidays)
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi,bdehning 

        Has your problem been solved?

         

        If it is solved, please share your solution, which will allow other community members who have the same problem as you to find a solution faster.

         

        If not, please share the pbix file except for the sensitive data, and then re-describe your needs, such as start and end dates, the days and days of the week you take off as a week, and the days of vacation in between, and what kind of results you expect. We'll be better suited to help you solve your problems.

         

         

        Best Regards,

        Leroy Lu