Calculate number of working days between two date columns

[yashponde]

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

[bongmw · Accepted solution]

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

[yashponde]

This works perfectly fine. Thanks @bongmw 

[Jihwan_Kim]

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.

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.

[yashponde]

Hi @Jihwan_Kim 

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

[bongmw]

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