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]
))

     

Anonymous's avatar
Anonymous
Not applicable
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]
))

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous , this appears to have fixed it! The calculation returns a blank if there is no end date. Thank you 🙂