Date Difference excluding weekends

[ssvr]

Hello,

I have a starting date and an ending date and I need to count only the working days in between. In other words, the date difference (DATEIFF) between the ending and the starting without taking into consideration the weekends

ReleaseDt             ClosedDt                  DaysDiff(Excludeweekends)

How can I achieve this?

Thank you,

[Anonymous]

Hi,

This maybe the solution:

https://www.youtube.com/watch?v=bs3yzmf9elAhttps://www.youtube.com/watch?v=bs3yzmf9elA

Regards,

[MarcoRotta]

Hi there. 

These solved threads might help you:

https://community.powerbi.com/t5/Desktop/Calculate-working-days-between-2-dates/td-p/230349

https://community.powerbi.com/t5/Desktop/DATEDIFF-Working-Days/td-p/130662

Regards.

[ssvr]

Thanks.

But here they suggested create a Date Table for Isweekday flag.

I worked on almost 10 years data so, cant possible to create Date Table with Isweekday flag ??

[v-yulgu-msft]

Hi @ssvr,

Yes. It is supported to create a calendar table with more than 10 years.

Regards,

Yuliana Gu

[ssvr]

Hi @Yuliana,

Thanks.

But its difficult to add 10 years canlender and we have to add for future dates also right ?

can you suggest any alternative for this requirement

[v-yulgu-msft]

Hi @ssvr,

You can create calendar table with CALENDAR() function like this:

Dim Table=CALENDAR(DATE(2008,1,1),DATE(2018,12,31))

Regards,

Yuliana Gu

[ssvr]

Thanks @v-yulgu-msft

I created a Date table:

Dim Table=CALENDAR(DATE(2008,1,1),DATE(2018,12,31))

I created a new column with is working day or not

is work day = SWITCH(WEEKDAY([Date]),1,0,7,0,1)

Now I want to create "DateDifference" Column with Createddate & Closeddate (I want to know day diffarence b/w these two dates excluding weekends)

CreatedDate   ClosedDate  DateDiffarence

Please guide me how to get this done

[ssvr]

@Phil_Seamark

Could you please resolve my issue.

I created a Date table:

Dim Table=CALENDAR(DATE(2008,1,1),DATE(2018,12,31))

I created a new column with is working day or not

is work day = SWITCH(WEEKDAY([Date]),1,0,7,0,1)

Now I want to create "DateDifference" Column with Createddate & Closeddate (I want to know day diffarence b/w these two dates excluding weekends)

CreatedDate   ClosedDate  DateDiffarence

[Anonymous]

As a measure (recommended, you'd have to make a strong case to make this a calculated column):

[Date Difference Measure] =
VAR CreateDate =
    MIN ( TableName[CreatedDate] )
VAR CloseDate =
    MIN ( TableName[ClosedDate] )
VAR CalendarNoWeekends =
    FILTER (
        CalendarTable,
        CalendarTable[Date] >= CreateDate
            && CalendarTable[Date] <= CloseDate
            && CalendarTable[Is Working Day] = "Yes"
    )
RETURN
    COUNTROWS ( CalendarNoWeekends )

As a calculated column (again, make sure you need to add a non-compressed column to your data model before going this way):

Date Difference Calculated Column =
VAR CreateDate = TableName[CreatedDate]
VAR CloseDate = TableName[ClosedDate]
VAR CalendarNoWeekends =
    FILTER (
        CalendarTable,
        CalendarTable[Date] >= CreateDate
            && CalendarTable[Date] <= CloseDate
            && CalendarTable[Is Working Day] = "Yes"
    )
RETURN
    COUNTROWS ( CalendarNoWeekends )

Only difference is you already have a row context, so you don't need to wrap the start and end dates in a MIN() function.

Hope this helps.

[PowerBInewb]

Did you ever find an answer to this? I have been struggling for a few days trying to accomplish the same measure.

In my case:

Call Close - Call open then filter out work days from a isweekday column on my date table. I can't seem to get any of these suggestions to work.

Thanks!

[Anonymous]

Something like this:

[Date Diff Measure] =
VAR Created =
    MAX ( Table[CreatedDate] )
VAR Closed =
    MAX ( Table[ClosedDate] )
RETURN
    CALCULATE (
        SUM ( 'Dim Table'[is work day] ),
        FILTER (
            ALL ( 'Dim Table'[DateColumn] ),
            'Dim Table'[DateColumn] >= Created
                && 'Dim Table'[DateColumn] <= Closed
        )
    )

[ssvr]

No Data shown in the Output column

[ssvr]

@Anonymous

Here SLA is measure !

Can you fix this error: 

[Anonymous]

Logical expressions that are added to CALCULATE() must be of the format Table[Column] = static expression.

If you want to do a more advanced logical expression, such as the following:

Table[Column] = [Measure]

[Measure] = static expression

[Measure] = [another Measure]

then you need to use FILTER() inside of CALCULATE()

Something like this:

[Count of SLA] =
CALCULATE (
    DISTINCTCOUNT ( Dates[CreateDt].[Date] ),
    FILTER (
        VALUES(Dates[CreateDt]),
        [SLA] = "Met SLA"
    )
)

The FILTER() parameter says this:

"Look at the distinct list of dates in the CreateDt column, based on the current filter context (as determined by slicers, etc.)  Go through that list line by line and evaluate the [SLA] measure.  Only keep those dates where [SLA] = "Met SLA"

Now you have a (likely) smaller list of values from the Dates[CreateDt] column.  That is the additional filter that is taken into account when CALCULATE performs the calculation...in this case counting the distinct number of values in the Dates[CreateDt] column.

[ssvr]

@Anonymous

Thanks ChrisHaas.

Its working great.

Can you fix the DateDiff excluding weekends DAX also

[ssvr]

@Anonymous

some error in DAX