Forum Discussion
vika160
Helper III
Helper IIICalculation days between dates excluding weekends and holidays
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 Is...
- 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)
AllisonKennedy
Community Champion
Community ChampionI 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
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
vika160no, 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.