cancel
Showing results for
Did you mean:
Power Participant

## 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)
1 ACCEPTED SOLUTION
Community Support

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

``````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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super User IV

@texmexdragon , Not very clear,

Can you create a diff column

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

Proud to be a Super User!

Power Participant

@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?

Community Support

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

``````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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements