Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate average working days between two dates, from a calculated column

I'm currently using the following equation to calculate the total working days between two dates: Total Working Days Column = SUMX ( FILTER ( 'Calendar', 'Calendar'[Date] >= Tas...
DAX Calculation
Filter
IF ELSE
Null
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous  I'm not able to reproduce the scenario. Please refer image. Can you please share some dummy data or else try to wrap your formula in if function as below

    Total Working Days Column = IF(Tasks[Task End Date]=BLANK(),BLANK(),
SUMX (
    FILTER (
        'Calendar',
        'Calendar'[Date] >= Tasks[Task Start Date]
            && 'Calendar'[Date] <= Tasks[Task End Date]
    ),
    'Calendar'[isWorkDay]
))

     

az38's avatar
az38
Community Champion
6 years ago

Hi Anonymous 

try

Total Working Days Column = 
(
SUM ('Calendar'[isWorkDay]),
    FILTER (
        'Calendar',
        'Calendar'[Date] >= Tasks[Task Start Date]
            && 'Calendar'[Date] <= Tasks[Task End Date]
    ),
    NOT(ISBLANK(Tasks[Task End Date]))
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

Anonymous's avatar
Anonymous
Not applicable
6 years ago

az38 , That function returned an error message stating that too many arguments were sent so I was not able to run it.