NETWORKDAYS using Date VAR

[roehler]

I'm trying to generate a "WorkingDays" column that calculates the NETWORKDAYS using the StartDate/EndDate values I've created in my Quarter's table.  I'm not getting an error, but it's still not working:

WorkingDays =

    VAR StartDate = SELECTEDVALUE(Quarters[StartDate])

    VAR EndDate = SELECTEDVALUE(Quarters[EndDate])

    RETURN

    NETWORKDAYS(StartDate, EndDate,1,Holidays)

I've tried using SELECTEDVALUE(Quarters[StartDate].[Date] and SELECTEDVALUE(Quarters[EndDate].[Date], but I keep getting NULL in WorkingDays.  Any ideas?

[roehler]

Thank you Owen, that was perfect.  I had added SELECTEDVALUE after my first attempt, and without it, it didn't work for some reason, but I removed it this morning and it worked fine.

[rohit1991]

Hi @roehler ,
The issue you're encountering—where the WorkingDays column returns null values despite not showing an error—is likely due to the misuse of the SELECTEDVALUE function within a calculated column context. SELECTEDVALUE is best suited for measures or visuals where there's a specific filter context.

In a calculated column, each row already has direct access to its respective values, so there's no need to use SELECTEDVALUE. Instead, you can refer directly to the [StartDate] and [EndDate] columns. Additionally, make sure that the Holidays table you're referencing is structured as a single-column table with proper date formatting. The corrected DAX should look like this:

WorkingDays = 
NETWORKDAYS(Quarters[StartDate], Quarters[EndDate], 1, Holidays[Date])

This version will calculate the working days for each row directly using the StartDate and EndDate values from the same row, along with your holiday list. Ensure the Holidays[Date] column contains valid date values and that the column is properly related to the date range you're analyzing.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar.

[OwenAuger · Accepted solution]

Hi @roehler 

You don't need SELECTEDVALUE.

The calculated column expression is evaluated in the row context of the Quarters table, so you can reference columns of that table directly.

WorkingDays =
VAR StartDate = Quarters[StartDate]
VAR EndDate = Quarters[EndDate]
RETURN
    NETWORKDAYS ( StartDate, EndDate, 1, Holidays )