Calculate Number of Business Days between two dates

[EMauterer]

Hoping someone can help.  I've setup a calendar table with a column for (Weekday), and another column that has a 1 for weekday, 0 for non weekday.  

I've joined that table to a Puchase Order table that has a column called [Actual Received Date].

In order to count the days from receipt date through Today's date to get an aging WITHOUT weekends, I'm setup a column with calculation as follows:

PO Business Day Age = calculate(count('Calendar'[Date]),filter('Calendar',DATESBETWEEN('Calendar'[Date],'PO PurchaseOrder'[Actual Received Date],Today())),filter('Calendar','Calendar'[Work Day]=1)

With this calc I'm getting the error "A table of multiple values was supplied where a single value was expected.The current operation was cancelled because another operation in the transaction failed."  

Any ideas on how I can make this work?

[Eric_Zhang · Accepted solution]

---

@EMauterer wrote:

Hoping someone can help.  I've setup a calendar table with a column for (Weekday), and another column that has a 1 for weekday, 0 for non weekday.  

I've joined that table to a Puchase Order table that has a column called [Actual Received Date].

In order to count the days from receipt date through Today's date to get an aging WITHOUT weekends, I'm setup a column with calculation as follows:

PO Business Day Age = calculate(count('Calendar'[Date]),filter('Calendar',DATESBETWEEN('Calendar'[Date],'PO PurchaseOrder'[Actual Received Date],Today())),filter('Calendar','Calendar'[Work Day]=1)

With this calc I'm getting the error "A table of multiple values was supplied where a single value was expected.The current operation was cancelled because another operation in the transaction failed."  

Any ideas on how I can make this work?

---

@EMauterer

I'll set 1 for weekday and 0 for weekend, then create a measure as below. See more details in the attached pbix file.

PO Business Day Age =
CALCULATE (
    SUM ( 'Calendar'[isWeekday] ),
    FILTER (
        'Calendar',
        'Calendar'[Date] >= MAX ( PurchaseOrder[Actual Received Date] )
            && 'Calendar'[Date] <= TODAY ()
    )
)

[ahxl]

Hi all

I have used the exact same Calendar table. 

I created a new table for calendar and created the following measure

CalculateWeekday = CALCULATE(SUM('Calendar'[isWeekday]),FILTER('Calendar','Calendar'[Date]>=MAX('AGINGTABLE'[Responded on])&&'Calendar'[Date]<=TODAY()))

Works great, so in 'AGINGTABLE' when i use this [Calculateweekday[ and [Tickets], it displays the number of days till today that the ticket has been opened.

However I have a column in 'TABLE X' called [Last Modified Date]. I need to only display the values where [Last Modified Date] = NULL. Whenever I bring this [Last Modified Date] column into the equation it displays all the values as '205' and when I try to filter on [Last Modified Date] = NULL, it brings up all the values in [Tickets] with just the two values where [Last Modified Date] = NULL but all the other values appear as well with the value '205' in the 'CalculateWeekday' field. 

So it will appear as 

Ticket No.  Last Modified Date CalculateWeekday

1                                                    13

2                 DATEVALUE              205

3                 DATEVALUE               205

4                                                   45

I only want it to display 'NULL' (blank) values, I have set the Visual Level filter for Last Modified Date as to only show blank values but its still displaying like this..

Does anyone know why this is happening? Please note [Last Modified Date] is from 'Table X' , a seperate table but I have created a relationship from 'Table X' to my main table 'AGINGTABLE'

Thanks

AHXL

[Anonymous]

Try this instead:

PO Business Day Age = CALCULATE(
	CountRows('Calendar'),
	ALL('Calendar'),
	'Calendar'[Date] >= [Actual Received Date],
	'Calendar'[Date] <= Today(),
	'Calendar'[Work Day] = 1
)