Exclude Start date in Datesbetween Function

[Anonymous]

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 = 1/14/2019

Days Diff = 6 (Need 5 exclude start Date)

[Anonymous]

Hi @Anonymous ,

Please try to use the function DATEDIFF：

Days = DATEDIFF ( All[SartDate], 'All'[EndDate], DAY )

Best Regards

Rena

[Anonymous]

Using DateDiff, how can I exclude weekends? 

[Anonymous]

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]

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 · Accepted solution]

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]

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().

[Anonymous]

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)

[Anonymous]

Using DateDiff, how can I exclude weekends? 

[Greg_Deckler]

Column = ([EndDate] - [StartDate]) * 1.

Measure = (MAX([EndDate]) - MAX([StartDate])) * 1.

[Anonymous]

Not Working