Calculation days between dates excluding weekends and holidays

[vika160]

I know there are tonns exaples here in a forum and on the web, but it seems that I find something for which I can not find a solution.

I have a fact table with dates and a tbldates table with the IsWorkingDayCode column that is set to 1 or 0 correctly.

It seems to be very easy, but I'm getting a mistake:

A column specified in the function call 'DATESBETWEEN' is not of type DATE. This is not supported

both columns are dates of fact table and are likned to tblDate

Calculated column:

Busness Days=
CALCULATE( COUNTROWS ( 'tblDate'),
DATESBETWEEN ( 'tblDate'[DateKey], V_PBI_APPLICATION_STATUS[Open Date],
IF (
V_PBI_APPLICATION_STATUS[App End Date] <> BLANK (),
V_PBI_APPLICATION_STATUS[App End Date],
TODAY ()
)
-1 ),
'tblDate'[IsWorkingDayCode] = TRUE ,
ALL ( V_PBI_APPLICATION_STATUS )
)

I was trying a lot of solutions without success.

Any appreciated help.

Thank you!

[AllisonKennedy]

I think the -1 might be part of the problem here.

Try:

Busness Days =
CALCULATE(
COUNTROWS ( 'tblDate'),
DATESBETWEEN ( 'tblDate'[DateKey], V_PBI_APPLICATION_STATUS[Open Date],
IF (
NOT(ISBLANK(V_PBI_APPLICATION_STATUS[App End Date])),
V_PBI_APPLICATION_STATUS[App End Date],
TODAY ()
)
),
'tblDate'[IsWorkingDayCode] = TRUE ,
ALL ( V_PBI_APPLICATION_STATUS )
) -1

[vika160]

no, I tried this before. I tried your code I still receive the same error.

thank you for a quick answer

[AllisonKennedy]

Do you want inclusive or exclusive of the open and end dates? Just add or remove the equal sign as needed in the inequalities below (as a new column in the V_PBI_APPLICATION_STATUS table): 

COLUMN = 

VAR _maxDate = IF (
NOT(ISBLANK(V_PBI_APPLICATION_STATUS[App End Date])),
V_PBI_APPLICATION_STATUS[App End Date],
TODAY ()
)

RETURN
COUNTROWS (FILTER( ALL('tblDate'),
'tblDate'[DateKey]>= V_PBI_APPLICATION_STATUS[Open Date]

&& 'tblDate'[DateKey]<=_maxDate
&& 'tblDate'[IsWorkingDayCode] = TRUE 
)) -1

[vika160]

it returns -1 for all rows.

[AllisonKennedy · Accepted solution]

Ok, this is most likely because the 'tblDate'[DateKey] is not in the same format as the V_PBI_APPLICATION_STATUS[App End Date]

They either both need to be DateKey (such as 20200714) or Date (such as July 14, 2020)

[vika160]

HI Allison,

I thought about it.

I have another column in Date table that is the same format as End and Start date. I relinked these fields and tried. 

I got the same error.

I can easily do this n sql but it is very frustrating I cannot figure this out in DAX. I am very new to DAX.

Thanks

[Greg_Deckler]

@vika160 - I created Net Work Days for this. https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109

I would avoid DATESBETWEEN - https://community.powerbi.com/t5/Quick-Measures-Gallery/To-bleep-With-DATESBETWEEN/m-p/1252805#M581