weird days between issue

[Anonymous]

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)

[amitchandak]

@Anonymous , Not very clear,

Can you create a diff column

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

[Anonymous]

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

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