Need help with my formula

[RvdHeijden]

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.

[AlbertoFerrari · Accepted solution]

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.com

[Abduvali]

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]

@Abduvali

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

[RvdHeijden]

@Abduvali

That wont work because my formula is more complicated because i only calculated with workingdays and if the EndDate is Blank it should use Today() 

But thanks for your input

[Abduvali]

@RvdHeijden

I think I got it follow the link to view file:

• Network Days

I have created a sample data in excel and used networkdays formula in excel to get ACTUAL WORKING DAYS so then I done the same but in power bi so NetWorkingDays is equivalent to Excels networkdays.

This should work with any model all you will have to do is to create full calendar table I called it "Date"

Let me know if it works for you.

Regards

Abduvali

[RvdHeijden]

@Abduvali

im sorry but i dont want to make a whole new formula especially because this one worked in the past.

Im just not sure why it returns no values anymore.

i Checked the relationship between the tables 'Date' and 'Schades' based upon the colum 'Date' and 'Binnengekomen' so that can't be the problem.

The first part in the formula looks if IsWorkday = 1 and that is also correct

Then it checks a bunch of other stuff and then it checks if the colums 'Binnengekomen' and 'Gefiatteerd' have values and they have. Even the datatype is set to Date/time

So where does it go wrong, can you answer me that ?

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 ()
)

[Abduvali]

@RvdHeijden

To get working days you have to you the following formula:

NetWorkingDays =
IF (
ISBLANK ( Sheet1[Date] ) || ISBLANK ( Sheet1[Close Date] ),
0,
IF (
Sheet1[Date] <=Sheet1[Close Date],
CALCULATE (
sum ( 'Date'[is work day]),
DATESBETWEEN ( 'Date'[Date], Sheet1[Date], Sheet1[Close Date] )
),
- CALCULATE (
COUNT ( 'Date'[is work day]),
DATESBETWEEN ( 'Date'[Date], Sheet1[Close Date], Sheet1[Date] )
      )
   )
)

As you need to get the sum of working days between 2 dates and not the date difference you trying to get.

I have replicated your formula and all it gives is a date difference:

So I think it's time for you accept and change your formula to the one above and get your model complete.

Regards

Abduvali

[RvdHeijden]

@Abduvali

ive used your formula but i think it has to be changed a bit because even now it doesn't return any values.

i think it goes wrong with the red part in the formula.

In my report i have a Calender table with 2 colums 'Date' and 'IsWorkday' and in the last column it returns either an 1 for workdays and a 0 for non-workdays.

So i think we have to change the formula to coop with this, but im not sure how to do so.

NetWorkingDays =
IF (
ISBLANK ( Schades[binnengekomen] ) || ISBLANK ( Schades[gefiatteerd] );
0;
IF (
Schades[binnengekomen] <=Schades[gefiatteerd];
CALCULATE (
sum ( 'Date'[IsWorkday]);
DATESBETWEEN ( 'Date'[Date]; Schades[binnengekomen]; Schades[gefiatteerd] )
);
- CALCULATE (
COUNT ( 'Date'[IsWorkday]);
DATESBETWEEN ( 'Date'[Date]; Schades[gefiatteerd]; Schades[binnengekomen] )
)
)
)

[Abduvali]

Step 1: Create new Table

• Calendar =
  CALENDAR (DATE(2016,1,1), DATE(2017,12,31)) //add whatever data range you want, you need to create this table it will not affect your model

Step 2: Create IsWorkday column under your Calendar Table

• IsWorkday = IF(WEEKDAY('Calendar'[Date],2)>5,0,1)

Step 3: Create relationship between your main table and new Calendar table 

Step 4: Create new column in your main table:

NetWorkingDays =
IF (
ISBLANK ( Schades[binnengekomen] ) || ISBLANK ( Schades[gefiatteerd] );
0;
IF (
Schades[binnengekomen] <=Schades[gefiatteerd];
CALCULATE (
sum ( 'Date'[IsWorkday]);
DATESBETWEEN ( 'Date'[Date]; Schades[binnengekomen]; Schades[gefiatteerd] )
);
- CALCULATE (
COUNT ( 'Date'[IsWorkday]);
DATESBETWEEN ( 'Date'[Date]; Schades[gefiatteerd]; Schades[binnengekomen] )
)
)
)

This way it will work if you having difficulty with that just send me your pbix =D I will try to fix for you but I would encourage you to do it yourself but if you exhaust yourself let me know and I will help you out.

Regards

Abduvali

[RvdHeijden]

@Abduvali

im glad for all your help but everything you say is already in my report 🙂

I have a Date table with the Calendar formula 

Date = Calendar("1/1/2016"; "12/31/2017")

i have an 'IsWorkday' formula, a whee bit different but basically the same

IsWorkday = IF ( WEEKDAY ( [Date]; 2 ) IN { 1; 2; 3; 4; 5 }; 1; 0 )

I also have the relationship but that is a many-to-one because i can have more then 1 ticket a day.

[Abduvali]

Ok delete the relationship and still should work

[AlbertoFerrari · Accepted solution]

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.com

[RvdHeijden]

@Abduvali

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()

@AlbertoFerrari

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 · Accepted solution]

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 🙂