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