Attempting to get Net days between 2 dates without weekends

[ianneg77]

I am attempting to calculate the number of work days in between my order date and invoice date. I have 2 main tables, one, a calendar table I created in Excel and uploaded to Power BI, named "Calendar - Excel" (it goes along with my June-July fiscal yr.) and two, a table of order info, named "Aftermarket Analysis".

• I created a calculated column in the my calendar table to flag workdays:

IsWorkDay = SWITCH(WEEKDAY('Calendar - Excel'[DATE],2),6,0,7,0,1)

• I then created another calculated column in my calendar table to do the solution:

Netdays = CALCULATE(SUM('Calendar - Excel'[IsWorkDay]),
DATESBETWEEN('Calendar - Excel'[DATE],'AfterMarket Analysis'[ORDER DATE],'AfterMarket Analysis'[INVOICE DATE]))

I then get the following error message:

A single value for column 'ORDER DATE' in table 'AfterMarket Analysis' 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.

What am I missing?

I do have a "many to 1" relationship from the Aftermarket table to the Calendar table. The common field is invoice DATE & DATE respectively.

[v-shex-msft · Accepted solution]

Hi @ianneg77,

You can try to use below calculate column to get available workday count from specific date columns.

WorkDays =
COUNTROWS (
    FILTER (
        CALENDAR (
            'AfterMarket Analysis'[ORDER DATE],
            'AfterMarket Analysis'[INVOICE DATE]
        ),
        WEEKDAY ( [Date], 2 ) < 6
    )
)

Regards,

Xiaoxin Sheng

[CoreyP]

I feel your pain, @ianneg77!

I recently struggled with a similar issue regarding order fulfillment analysis. Unlike you, I had just ONE date and I needed to add either 1 or 3 working days (non weekend, non holiday) depending on the order type, to get a required ship by date.

It was a nightmare! But, fortunately, the people of these forums are the smartest, bi, gurus you'll ever meet.

Anyway, while I was scouring the internet for a solution to my problem, I found a lot of results for calculating working days between 2 dates. Check out this thread: https://community.powerbi.com/t5/Desktop/NETWORKDAYS-type-function-in-PowerBI/td-p/154902