Date Difference between 2 dates for the next day of the first date excluding weekends

[suebayes]

I'm trying to calculate the date difference between two fields excluding weekends which I think I have achieved, however, I want the StartDate to start one day after the received date.  I can't use NextDay or DateAdd in my StartDate VAR as max only accepts one variable.  Any help gratefully appreciated.

Difference between received and registered measure =

VAR StartDate = CALCULATE(MAX('Planning Applications'[Received Date]))

VAR EndDate = CALCULATE(max('Planning Applications'[Registered Date]))

VAR Calendar1 = CALENDAR(StartDate, EndDate)

VAR Calendar2 = ADDCOLUMNS(Calendar1, "Weekday", Weekday([Date], 2))

VAR IncludingReceivedDAte = COUNTX(FILTER(Calendar2, [Weekday] < 6), [Date])

RETURN IncludingReceivedDAte

[v-alq-msft · Accepted solution]

Hi, @suebayes 

Based on your description, I created data to reproduce your scenario.

Planning Applications:

You may create two measures as below. One is for the original result and another is for the result whose start date is the next day after the received date.

Unchanged Result = 
var _startdate = SELECTEDVALUE('Planning Applications'[Received Date])
var _enddate = SELECTEDVALUE('Planning Applications'[Regisered Date])
return
IF(
    NOT(ISBLANK(_startdate))&&NOT(ISBLANK(_enddate)),
    COUNTROWS(
        FILTER(
            CALENDAR(
                _startdate,
                _enddate
            ),
            NOT(WEEKDAY([Date]) in {1,7})
        )
    )
)

Result = 
var _receiveddate = SELECTEDVALUE('Planning Applications'[Received Date])
var _startdate = _receiveddate+1
var _enddate = SELECTEDVALUE('Planning Applications'[Regisered Date])
return
IF(
    NOT(ISBLANK(_startdate))&&NOT(ISBLANK(_enddate)),
    var result = 
    COUNTROWS(
        FILTER(
            CALENDAR(
                _startdate,
                _enddate
            ),
            NOT(WEEKDAY([Date]) in {1,7})
        )
    )
    return
    IF(
        ISBLANK(result),
        0,
        result
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[suebayes]

Thank you @v-alq-msft , that's really helpful.  I hadn't thought to use SelectedValue but makes more sense.  Also really like the if statement for when there are no dates for both received and registered.

It's thrown up an issue for when received and registered are on the same date, which could happen, I think it would have to be another if statement outside of the calendar table, to give a result as 0 showing less than a day to turn round the application.

I've tried adding an or statement to the first If statement but it's not working, any thoughts?

Result =

var _receiveddate = SELECTEDVALUE('Planning Applications'[Received Date])

var _startdate = _receiveddate+1

var _enddate = SELECTEDVALUE('Planning Applications'[Registered Date])

return

IF(

NOT(ISBLANK(_startdate))&&NOT(ISBLANK(_enddate)) || (_startdate = _enddate),

var result =

COUNTROWS(

FILTER(

CALENDAR(

_startdate,

_enddate

),

NOT(WEEKDAY([Date]) in {1,7})

)

)

return

IF(

ISBLANK(result),

0,

result

)

)

[amitchandak]

@suebayes , Please check working day diff -https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

[suebayes]

@amitchandak thank you for the reply Amit, really appreciate it.  I can't see where you would add another day as part of the measure to the StartingDay but that might be me not seeing it properly, Sue