How to count working days between two dates within the same table

[ThomasSan]

Hi everyone,

I am attempting to create a measure per row which counts the working days between two days within the same table. To do that, I found the following guide:
https://blog.enterprisedna.co/calculate-workdays-between-two-dates-in-power-bi/

However, when I follow it, I get the error message "A single value for column 'DateOfDelivery' in table 'Deliveries' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.". Unfortunately, I am unsure how to address this problem adequately. The way I see it, there is always just one 'DateOfDelivery' per row so I do not understand how this single value cannot be determined per row.

Here is the table that I am using:

Here, it is possible that one order may result in several deliveries.

And this is my date table:

I am using the following DAX expression:

PassedDays1 = 
CALCULATE(
    COUNTROWS('Date'),
    DATESBETWEEN(
        'Date'[Date],
        Deliveries[DateOfDelivery],
        Deliveries[OrderDate]
    ),
    'Date'[IsWeekday] = TRUE
)

Does anyone know what/where my error is?

[johnt75 · Accepted solution]

You're adding it as a measure, not a calculated column, therefore it doesn't have a row context and doesn't know which delivery date or order date to use. 

If you use the same code as a calculated column it will work, but since that blog article was published there is now a NETWORKDAYS function in DAX, so you could just use that instead.

Working days column = NETWORKDAYS(Deliveries[Order date], Deliveries[Delivery date])

[ThomasSan]

@johnt75 Thanks, I did not know about NETWORKDAYS before. It worked perfectly!