Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Anonymous
Not applicable

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] >= Tasks[Task Start Date]
            && 'Calendar'[Date] <= Tasks[Task End Date]
    ),
    'Calendar'[isWorkDay]
)

Where the table Calendar, is a table with all dates listed and a column of isWorkDay, to indicate if it is Mon-Fri and not Sat or Sun.

 

This calculation is working for each row, except for the case when my Task End Date is an empty field.

Instead of returning NULL for the Total Workday Days Column, I'm receiving a random high value like 5042 or something similar (when instead it should not be able to calculate it because there is only a start date and no end date). I would like to receive a NULL or empty value so that when I calculate an average on my Total Working Days Column, I get an accurate number that excludes my nulls.  Is there a way to add that into my column calculation or do I need to use some other filtering method?

 

Thank you for your help!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

blank.png

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

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

blank.png

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
Not applicable

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

az38
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.