Forum Discussion

ssvr's avatar
ssvr
Helper III
8 years ago

Date Difference excluding weekends

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 tak...
Need Help
ssvr's avatar
ssvr
Helper III
8 years ago

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    ssvr
    Helper III
    8 years ago

    Anonymous

     

    some error in DAX

     

  • ssvr's avatar
    ssvr
    Helper III
    8 years ago

    No Data shown in the Output column

     

  • ssvr's avatar
    ssvr
    Helper III
    8 years ago

    Anonymous

     

    Here SLA is measure !

     

    Can you fix this error: 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
    ssvr
    Helper III
    8 years ago

    Anonymous

     

    Thanks ChrisHaas.

     

    Its working great.

     

    Can you fix the DateDiff excluding weekends DAX also