Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

weird days between issue

Hello Community  -   Confused as to why my formula is returning the following results.  

 

The formula is calculating the days elapsed between the Order Date and the FCHR date.   I wanted to ensure that if the dates are both the same....it should be considered a zero value  (i.e.  same  day).     But if there is one day between, it should be just one day. 

 

What is strange is that the first row below, shows a -1 day, despite the Order Date and FCHR Date being on the 18th.    The value I would expect is zero.....the same as it is on the 4th row down.     So I am equally confused why it shows this value on one set of dates, but not on another.   

 

Order vs FCHR Weekday Count = IF('Flu Shipped'[FCHR Date]=BLANK(),0,
IF( 'Flu Shipped'[Order Date] <= 'Flu Shipped'[FCHR Date],
CALCULATE(
COUNTROWS( DateTable ),
DATESBETWEEN ( DateTable[Date], 'Flu Shipped'[Order Date], 'Flu Shipped'[FCHR Date]),DateTable[DateIsWorkingDay]= True,
ALL ( 'Flu Shipped' )
)-1,

CALCULATE(
COUNTROWS( DateTable ),
DATESBETWEEN ( DateTable[Date], 'Flu Shipped'[Order Date], 'Flu Shipped'[FCHR Date]), DateTable[DateIsWorkingDay]=True,
ALL ( 'Flu Shipped' )
) * -1)+0)
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    5 years ago

    hi  Anonymous 

    To my understand, you could use this formula to get it:

    Order vs FCHR Weekday Count = IF('Flu Shipped'[FCHR Date]=BLANK(),0,
IF( 'Flu Shipped'[Order Date] <= 'Flu Shipped'[FCHR Date],
COUNTROWS( FILTER(DateTable,DateTable[Date]>='Flu Shipped'[Order Date]&&DateTable[Date]<'Flu Shipped'[FCHR Date]&&DateTable[DateIsWorkingDay]=TRUE())),
COUNTROWS( FILTER(DateTable,DateTable[Date]>='Flu Shipped'[Order Date]&&DateTable[Date]<'Flu Shipped'[FCHR Date]&&DateTable[DateIsWorkingDay]=TRUE())) * -1)+0)

    or

    adjust your formula as below:

    Order vs FCHR Weekday Count 2 = 
var  days=IF(CALCULATE(
COUNTROWS( DateTable ),
DATESBETWEEN ( DateTable[Date], 'Flu Shipped'[Order Date], 'Flu Shipped'[FCHR Date]),DateTable[DateIsWorkingDay]= True,
ALL ( 'Flu Shipped' )
)=BLANK(),1,CALCULATE(
COUNTROWS( DateTable ),
DATESBETWEEN ( DateTable[Date], 'Flu Shipped'[Order Date], 'Flu Shipped'[FCHR Date]),DateTable[DateIsWorkingDay]= True,
ALL ( 'Flu Shipped' )
)) 
return

IF('Flu Shipped'[FCHR Date]=BLANK(),0,
IF( 'Flu Shipped'[Order Date] <= 'Flu Shipped'[FCHR Date],
days-1,

days * -1)+0)

     

    Regards,

    Lin

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Not very clear,

    Can you create a diff column

     

    Date diff = datediff([Order Date] , [FCHR date],day)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak  Yes, this works, but needs to take into account only week days.    Is there a way to incorporate your formula into mine to ensure that only week days are counted?

       

       

       

       

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        5 years ago

        hi  Anonymous 

        To my understand, you could use this formula to get it:

        Order vs FCHR Weekday Count = IF('Flu Shipped'[FCHR Date]=BLANK(),0,
IF( 'Flu Shipped'[Order Date] <= 'Flu Shipped'[FCHR Date],
COUNTROWS( FILTER(DateTable,DateTable[Date]>='Flu Shipped'[Order Date]&&DateTable[Date]<'Flu Shipped'[FCHR Date]&&DateTable[DateIsWorkingDay]=TRUE())),
COUNTROWS( FILTER(DateTable,DateTable[Date]>='Flu Shipped'[Order Date]&&DateTable[Date]<'Flu Shipped'[FCHR Date]&&DateTable[DateIsWorkingDay]=TRUE())) * -1)+0)

        or

        adjust your formula as below:

        Order vs FCHR Weekday Count 2 = 
var  days=IF(CALCULATE(
COUNTROWS( DateTable ),
DATESBETWEEN ( DateTable[Date], 'Flu Shipped'[Order Date], 'Flu Shipped'[FCHR Date]),DateTable[DateIsWorkingDay]= True,
ALL ( 'Flu Shipped' )
)=BLANK(),1,CALCULATE(
COUNTROWS( DateTable ),
DATESBETWEEN ( DateTable[Date], 'Flu Shipped'[Order Date], 'Flu Shipped'[FCHR Date]),DateTable[DateIsWorkingDay]= True,
ALL ( 'Flu Shipped' )
)) 
return

IF('Flu Shipped'[FCHR Date]=BLANK(),0,
IF( 'Flu Shipped'[Order Date] <= 'Flu Shipped'[FCHR Date],
days-1,

days * -1)+0)

         

        Regards,

        Lin