Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
@Anonymous , Not very clear,
Can you create a diff column
Date diff = datediff([Order Date] , [FCHR date],day)
@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 @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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |