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

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)