Forum Discussion

vika160's avatar
vika160
Icon for Helper III rankHelper III
6 years ago
Solved

Calculation 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...
  • AllisonKennedy's avatar
    AllisonKennedy
    6 years ago
    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's avatar
vika160
Icon for Helper III rankHelper III
6 years ago

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

thank you for a quick answer

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
    vika160
    Icon for Helper III rankHelper III
    6 years ago

    it returns -1 for all rows.

    • AllisonKennedy's avatar
      AllisonKennedy
      Icon for Community Champion rankCommunity Champion
      6 years ago
      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's avatar
        vika160
        Icon for Helper III rankHelper III
        6 years ago

        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