Forum Discussion
Post ProdigyNeed help with my formula
Well, if you need to compute the formula for the number of working days between open and close, then your code is not correct. What you are computing is the number of days between the two dates, blanking it in the very special case when the date on which you base the relationship is not a working day. Thus, sorry to say that, but your code looks wrong.
With that said, the error message seems to indicate that there is something wrong with the data, a refresh of the model might fix it (never seen it, but this is what the error message says), the formula - although semantically wrong - looks fine.
If you need to compute the difference, in working days, between the two dates, you can easily build a calculated column like this (I wrote the code using Contoso, so it might be different in your specific case)
Delta in Working Days = VAR StartDate = Sales[Order Date] VAR EndDate = Sales[Delivery Date] RETURN CALCULATE ( COUNTROWS ( 'Date' ), DATESBETWEEN( 'Date'[Date], StartDate, EndDate ), 'Date'[Working Day] = "Workday" )Worth to note that DATESBETWEEN ignores the relationship, so you can (and should) keep it active, otherwise the report will become wrong, because of the missing relationship.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.comJust replace EndDate with this:
VAR EndDate = IF ( ISBLANK ( Sales[Delivery Date] ), TODAY (), Sales[Delivery Date] )
I created the two variables for this purpose, then I forgot to use them :)
Skilled SharerOk delete the relationship and still should work
Most Valuable ProfessionalWell, if you need to compute the formula for the number of working days between open and close, then your code is not correct. What you are computing is the number of days between the two dates, blanking it in the very special case when the date on which you base the relationship is not a working day. Thus, sorry to say that, but your code looks wrong.
With that said, the error message seems to indicate that there is something wrong with the data, a refresh of the model might fix it (never seen it, but this is what the error message says), the formula - although semantically wrong - looks fine.
If you need to compute the difference, in working days, between the two dates, you can easily build a calculated column like this (I wrote the code using Contoso, so it might be different in your specific case)
Delta in Working Days =
VAR StartDate = Sales[Order Date]
VAR EndDate = Sales[Delivery Date]
RETURN
CALCULATE (
COUNTROWS ( 'Date' ),
DATESBETWEEN( 'Date'[Date], StartDate, EndDate ),
'Date'[Working Day] = "Workday"
)
Worth to note that DATESBETWEEN ignores the relationship, so you can (and should) keep it active, otherwise the report will become wrong, because of the missing relationship.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
- RvdHeijden
It returns values when i delete the relationship and as far as i can see it returns the correct value.
The only problem is dat if the enddate is blank it returns the value '0'.
In my original formula i put a line in that IF the EndDate is BLANK() then it should use TODAY() and calculate the difference between Begin and TODAY()
Your formula is totally different but returns the same value as the formula of Abduvali.
But your formula returns strange values if the EndDate is blank so either way we need to change the formula so that if the EndDate is Blank it should use TODAY() as EndDate
- AlbertoFerrari
Just replace EndDate with this:
VAR EndDate = IF ( ISBLANK ( Sales[Delivery Date] ), TODAY (), Sales[Delivery Date] )
I created the two variables for this purpose, then I forgot to use them :)
