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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
yashponde
New Member

Calculate number of working days between two date columns

Hi guys,

 

I need to calculate the number of days a case was delayed. I have 2 date columns in my main table 'Resolution Date' and 'Resolution Due By'.

 

I have created a column in the date table which mentions if it is a working day or no.

 

I need to create a column that calculated the delay (working days only) with the following logic:

 

if(resolution date = blank(), if(today()>resolution due by,(today()-resolution due by),0),(resolution date-resolution due date))

 

yashponde_0-1617145497511.png

 

 

 

 

1 ACCEPTED SOLUTION
bongmw
Helper I
Helper I

Just reread your question, and tweaked the DAX a little so that it substitutes any blank in Resolution Date with the current date, and then works out the delay. One important thing to point out is that you will need to have your date table up to the current date or the DAX will return an error of "An invalid numeric representation of a date value was encountered"

 

 

=
VAR ResolutionDate = IF(ISBLANK(Resolution[Resolution Date]),TODAY(),Resolution[Resolution Date])
RETURN
IF (
ResolutionDate > Resolution[Resolution Due By],
CALCULATE (
COUNTROWS ( 'Date' ),
DATESBETWEEN (
'Date'[Date],
Resolution[Resolution Due By],
ResolutionDate - 1
),
'Date'[IsWorkingDay] = 1,
ALL ( Resolution )
),
- CALCULATE (
COUNTROWS ( 'Date' ),
DATESBETWEEN (
'Date'[Date],
ResolutionDate ,
Resolution[Resolution Due By] - 1
),
'Date'[IsWorkingDay] = 1,
ALL ( Resolution )
)
)

View solution in original post

5 REPLIES 5
bongmw
Helper I
Helper I

Just reread your question, and tweaked the DAX a little so that it substitutes any blank in Resolution Date with the current date, and then works out the delay. One important thing to point out is that you will need to have your date table up to the current date or the DAX will return an error of "An invalid numeric representation of a date value was encountered"

 

 

=
VAR ResolutionDate = IF(ISBLANK(Resolution[Resolution Date]),TODAY(),Resolution[Resolution Date])
RETURN
IF (
ResolutionDate > Resolution[Resolution Due By],
CALCULATE (
COUNTROWS ( 'Date' ),
DATESBETWEEN (
'Date'[Date],
Resolution[Resolution Due By],
ResolutionDate - 1
),
'Date'[IsWorkingDay] = 1,
ALL ( Resolution )
),
- CALCULATE (
COUNTROWS ( 'Date' ),
DATESBETWEEN (
'Date'[Date],
ResolutionDate ,
Resolution[Resolution Due By] - 1
),
'Date'[IsWorkingDay] = 1,
ALL ( Resolution )
)
)

This works perfectly fine. Thanks @bongmw 

Jihwan_Kim
Super User
Super User

Hi, @yashponde 

Please correct me if I wrongly understood your question.

I assume today() = selected date from a slicer.

 

Please check the below picture if that is what you are looking for.

The sample pbix file's link is down below, and the measure is in the file as well.

 

Picture1.png

 

https://www.dropbox.com/s/6l7bxkn50mzd6f7/yashponde.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Hi @Jihwan_Kim 

 

Today refers to the current date (eg:31/03/2021)

bongmw
Helper I
Helper I

Hi @yashponde 

 

You can refer to this link from SQLBI on counting the number of working days.

 

In your case, however there are a few additional logic to take care of - cases where the Resolution Date could be blank, and also cases where Resolution Date is greater than Resolution Due By date, and vice versa.

 

A simple way is just to take care of these with IF statements. A calculated column would be like below. Also note that your date table needs to cover the full range of possible dates in Table1 (which I have named Resolution table in my code below)

 

=IF (
    NOT ISBLANK ( Resolution[Resolution Date] ),
    IF (
        Resolution[Resolution Date] > Resolution[Resolution Due By],
        CALCULATE (
            COUNTROWS ( 'Date' ),
            DATESBETWEEN (
                'Date'[Date],
                Resolution[Resolution Due By],
                Resolution[Resolution Date] - 1
            ),
            'Date'[IsWorkingDay] = 1,
            ALL ( Resolution )
        ),
        CALCULATE (
            COUNTROWS ( 'Date' ),
            DATESBETWEEN (
                'Date'[Date],
                Resolution[Resolution Date],
                Resolution[Resolution Due By] - 1
            ),
            'Date'[IsWorkingDay] = 1,
            ALL ( Resolution )
        )
    )
)

 

Capture.JPG

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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