Forum Discussion

HearnTexas's avatar
HearnTexas
New Member
1 year ago
Solved

Why is NETWORKDAYS Not working for me correctly?

Hello,

I'm new to posting questions in this community. I can usually find my answer, but I cannot find why this DAX measure is not working. Below is my measure and below the measure is an image of what it is returning. 

 

Days Left in Fiscal Year =
VAR CurrentDate = 'Calendar TB'[Date]
VAR FiscalYearEnd =
    IF(
        MONTH(CurrentDate) >= 10, // If the current month is Oct or later (e.g., Q1 of new fiscal year)
        DATE(YEAR(CurrentDate) + 1, 9, 30), // Fiscal year ends Sept 30th of the next calendar year
        DATE(YEAR(CurrentDate), 9, 30) // Fiscal year ends Sept 30th of the current calendar year
    )
VAR Holidays =
    CALCULATETABLE(
        SELECTCOLUMNS(
            'Calendar TB',
            'Calendar TB'[Federal Holiday] // Extracting only the date column
        ),
        'Calendar TB'[IsHoliday] = TRUE // Or 'Calendar TB'[IsHoliday] = 1
    )
RETURN
    NETWORKDAYS(CurrentDate, FiscalYearEnd, 1, Holidays)
 

The year starts off on the weekend and it calculates the number of days correctly. When it gets to the Holiday it's subtracting a day and it should be 255 like the weekend, then the 253 should be 254 and so on,

It's doing this for every holiday that follows a weekend or if the holiday is in the middle of the week it skips a number. see below.

In the image below it skipped a number.

How can I fix this problem? Your help is greatly appreciated!!

Thank you,

Hearntexas

 
  • HearnTexas's avatar
    HearnTexas
    1 year ago

    I solved my own problem with the help of Google. Here is the correct DAX measure. 

    Days Left in Fiscal Year =
    VAR CurrentDate = 'Calendar TB'[Date]
    VAR FiscalYearEnd =
        IF(
            MONTH(CurrentDate) >= 10, // If the current month is Oct or later (e.g., Q1 of new fiscal year)
            DATE(YEAR(CurrentDate) + 1, 9, 30), // Fiscal year ends Sept 30th of the next calendar year
            DATE(YEAR(CurrentDate), 9, 30) // Fiscal year ends Sept 30th of the current calendar year
    )
    VAR Holidays =
        CALCULATETABLE (
            VALUES ( 'Calendar TB'[Date] ),
            FILTER (
                'Calendar TB',
                'Calendar TB'[IsHoliday] = TRUE() &&
                'Calendar TB'[Date] >= CurrentDate &&
                'Calendar TB'[Date] <= FiscalYearEnd
            )
        )
    RETURN
    NETWORKDAYS(CurrentDate, FiscalYearEnd, 1, Holidays)
    This is what it returns:

     

     

    Thank you again for trying to help me!!

    HearnTexas

     

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    HearnTexas , Try using

     

    Days Left in Fiscal Year =
    VAR CurrentDate = 'Calendar TB'[Date]
    VAR FiscalYearEnd =
    IF(
    MONTH(CurrentDate) >= 10, // If the current month is Oct or later (e.g., Q1 of new fiscal year)
    DATE(YEAR(CurrentDate) + 1, 9, 30), // Fiscal year ends Sept 30th of the next calendar year
    DATE(YEAR(CurrentDate), 9, 30) // Fiscal year ends Sept 30th of the current calendar year
    )
    VAR Holidays =
    CALCULATETABLE(
    SELECTCOLUMNS(
    'Calendar TB',
    'Calendar TB'[Federal Holiday] // Extracting only the date column
    ),
    'Calendar TB'[IsHoliday] = TRUE // Or 'Calendar TB'[IsHoliday] = 1
    )
    VAR AdjustedHolidays =
    ADDCOLUMNS(
    Holidays,
    "AdjustedDate",
    IF(
    WEEKDAY('Calendar TB'[Federal Holiday]) = 1, // If the holiday falls on a Sunday
    'Calendar TB'[Federal Holiday] + 1, // Adjust to the following Monday
    IF(
    WEEKDAY('Calendar TB'[Federal Holiday]) = 7, // If the holiday falls on a Saturday
    'Calendar TB'[Federal Holiday] - 1, // Adjust to the previous Friday
    'Calendar TB'[Federal Holiday] // Keep the original date
    )
    )
    )
    RETURN
    NETWORKDAYS(CurrentDate, FiscalYearEnd, 1, AdjustedHolidays)

    • HearnTexas's avatar
      HearnTexas
      New Member
      1 year ago

      Thank you for taking the time to help me!!

       

      The measure you provided above is not working. I'm receiving and error on "AdjustedDate", see screen shot below.

       

      I don't think the problem is the holiday falling on a weekend because I've already adjusted that in my Federal Holiday column the date reflects the actual holiday date or the observed date.

       

      Thank you again!!

      HearnTexas

      • HearnTexas's avatar
        HearnTexas
        New Member
        1 year ago

        I solved my own problem with the help of Google. Here is the correct DAX measure. 

        Days Left in Fiscal Year =
        VAR CurrentDate = 'Calendar TB'[Date]
        VAR FiscalYearEnd =
            IF(
                MONTH(CurrentDate) >= 10, // If the current month is Oct or later (e.g., Q1 of new fiscal year)
                DATE(YEAR(CurrentDate) + 1, 9, 30), // Fiscal year ends Sept 30th of the next calendar year
                DATE(YEAR(CurrentDate), 9, 30) // Fiscal year ends Sept 30th of the current calendar year
        )
        VAR Holidays =
            CALCULATETABLE (
                VALUES ( 'Calendar TB'[Date] ),
                FILTER (
                    'Calendar TB',
                    'Calendar TB'[IsHoliday] = TRUE() &&
                    'Calendar TB'[Date] >= CurrentDate &&
                    'Calendar TB'[Date] <= FiscalYearEnd
                )
            )
        RETURN
        NETWORKDAYS(CurrentDate, FiscalYearEnd, 1, Holidays)
        This is what it returns:

         

         

        Thank you again for trying to help me!!

        HearnTexas