cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
texmexdragon
Power Participant
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.   

texmexdragon_0-1601862690122.png

 

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

hi  @texmexdragon 

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

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.

View solution in original post

3 REPLIES 3
amitchandak
Super User IV
Super User IV

@texmexdragon , Not very clear,

Can you create a diff column

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

 

 

 

 

hi  @texmexdragon 

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

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.

View solution in original post

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.