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 = 1/14/2019
Days Diff = 6 (Need 5 exclude start Date)
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

10 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

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

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Not Working

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    Please try to use the function DATEDIFF：

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

    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

      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