Why is NETWORKDAYS Not working for me correctly?

[HearnTexas]

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

[bhanu_gautam]

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

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 · Accepted solution]

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