Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Exclude Start date in Datesbetween Function

How to exclude start date in below function Days= CALCULATE( COUNTROWS(DateDim) , DATESBETWEEN( DateDim[ActualDate], All[SartDate],'All'[EndDate]), All(All) ) EX: StartDate = 1/9/2019 EndDate =...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

    The formula "weekday(DateDim[ActualDate],2)<=5) " is used to get the dates which is working day. You can refer this documentation about the details of function WEEKDAY .

    You can update the formula of the related calculated column as below:

    Daysdiff=
var a= DATEDIFF('All'[StartDate],'All'[EndDate], DAY) -
(
   CALCULATE (
        COUNTROWS('DateDim'),
        WEEKDAY('DateDim'[ActualDate],2)>5,
        DATESBETWEEN('DateDim'[ActualDate], 'All'[StartDate], 'All'[EndDate])
   )
) 
return 
if(a<0,0,a)

    Best Regards

    Rena

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Using DateDiff, how can I exclude weekends? 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

What is your expected result? You want the number of days between StartDate and EndDate exclude weekends? If yes, please check if the following screenshot is your expected result?

And you refer the start date need to be excluded, then the final days between 1/9/2019 and 1/14/2019 should be 3 not 5 since it need to exclude weekends and start date....

Best Regards

Rena

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Yes expected result is 3. Could you explain why you used

    weekday(DateDim[ActualDate],2)<=5)  at the end.
     
    Also using DateDiff is there a way to exclude Weekends? As I dont want any hardcoding like (-1) in the syntax

    Thanks 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

      The formula "weekday(DateDim[ActualDate],2)<=5) " is used to get the dates which is working day. You can refer this documentation about the details of function WEEKDAY .

      You can update the formula of the related calculated column as below:

      Daysdiff=
var a= DATEDIFF('All'[StartDate],'All'[EndDate], DAY) -
(
   CALCULATE (
        COUNTROWS('DateDim'),
        WEEKDAY('DateDim'[ActualDate],2)>5,
        DATESBETWEEN('DateDim'[ActualDate], 'All'[StartDate], 'All'[EndDate])
   )
) 
return 
if(a<0,0,a)

      Best Regards

      Rena

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks for the solution. It Worked, but for some reason "weekday(DateDim[ActualDate],2)<=5)  is not working for me, so instead I used 'DateDim'[IsWeekend] = TRUE().

        Daysdiff=
        var a= DATEDIFF('All'[StartDate],'All'[EndDate], DAY) -
        (
        CALCULATE (
        COUNTROWS('DateDim'),

        DATESBETWEEN('DateDim'[ActualDate], 'All'[StartDate], 'All'[EndDate]),
        'DateDim'[IsWeekend] = TRUE()
        )
        )
        return
        if(a<0,0,a)