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

     
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