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 w...
  • 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

     
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