Forum Discussion
Post ProdigyNeed help with my formula
This is a strange one because my formula did work but now it returns an error.
This is my formula:
Doorlooptijd =
IF (
RELATED ( 'Date'[IsWorkday] ) = 1;
IF (
schades[binnengekomen] = Schades[gefiatteerd];
1;
IF (
ISBLANK ( Schades[gefiatteerd] );
DATEDIFF ( Schades[binnengekomen]; TODAY (); DAY );
DATEDIFF ( Schades[binnengekomen]; Schades[gefiatteerd]; DAY )
)
);
BLANK ()
)
And the error is:
Column '' in Table '' contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
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 :)
14 Replies
- Abduvali
Skilled Sharer
Hi RvdHeijden,
If you looking to get a date difference it will require both FROM-TO dates if one of them is missing you will get an error.
- Can you be more specific and describe what are you trying to achieve by checking IsWorkday???
The only thing I have in mind is you can try the following insert another IF statement saying IF ( DAY = BLANK(), TODAY(), DAY() ) this might help.
Regards
Abduvali
- RvdHeijden
This is great, because when i closed my report and reopend it the error was gone.
However now it doesn't return any values.
Doorlooptijd =
IF (
RELATED ( 'Date'[IsWorkday] ) = 1;
IF (
schades[binnengekomen] = Schades[gefiatteerd];
1;
IF (
ISBLANK ( Schades[gefiatteerd] );
DATEDIFF ( Schades[Binnengekomen]; TODAY (); DAY );
DATEDIFF ( Schades[Binnengekomen]; Schades[gefiatteerd]; DAY )
)
);
BLANK ()
)i dont understand wy because both colums have values.
The reason i want to calculate the DateDiff is because i want to know how long a tickets is 'open' when a ticket is made we have a begindate [Binnengekomen] and when the ticket is closed we have an EndDate [gefiatteerd].
then i need to calculate the number of days that ticket was 'open' to calculate an average on how long that ticket was open.
Hopefully this wil explain my reason
- Abduvali
Column = DATEDIFF(Query1[Date],Query1[Close Date],day)
This works for me so maybe might help. This shown number of days between 2 dates
