topic Re: Calculated columns that outputs the difference in workdays shall only run if there are 2 dates in DAX Commands and Tips

Calculated columns that outputs the difference in workdays shall only run if there are 2 dates

teflonreis — Thu, 02 Jun 2022 09:32:17 GMT

Hello everyone

I have two columns with dates. And have added a calculated date column that gives me the difference between the the dates in working days. So far so good.

CalculatedColumn	date1	date2
0
0		5/23/2022
0
0
-2	5/23/2022
0
0
2	5/30/2022	6/1/2022
0

However you probably noticed the problem. The calculated column magically makes up differences when there are no data or one date missing. My two columns have lots of empy cells. In case of two empty cells, calculated columns gives me 0-0=0 days.

In case of only one date and one empty cell, if even gives me negatives number of days.

I fail at adding a condition such that it only outputs the difference if there really are two date dates.

Since I want to compute the average number of working days in a Report, I get a wrong average (negative number of days).

Really am stuck and would appreciate some help.

This is the condition I am thinking about. However never used variables before.

IF (AND(Table[date1]<>0, Table[date2]<>0), VAR CalculatedColumn, "Error or do nothing and dont go into average measure")

And this is the CalculatedColumn formula that I currently use. I would make it a VAR Variable and add an IF statement. Again, never done that before.

CalculatedColumn = ( DATEDIFF ( Table[date1], Table[date2], DAY ) ) - INT ( DATEDIFF ( Table[date1], Table[date2], DAY ) / 7 ) * 2 - IF ( WEEKDAY ( Table[date2] ) < WEEKDAY ( Table[date1] ), 2, IF ( OR ( WEEKDAY ( Table[date2] ) = 7, WEEKDAY ( Table[date1] ) = 1 ), 1, 0 ) )

Re: Calculated columns that outputs the difference in workdays shall only run if there are 2 dates

tamerj1 — Thu, 02 Jun 2022 09:50:25 GMT

Hi teflonreis
Replace

AND(Table[date1]<>0, Table[date2]<>0)

with

AND(Table[date1 > 0, Table[date2] > 0)

Re: Calculated columns that outputs the difference in workdays shall only run if there are 2 dates

teflonreis — Thu, 02 Jun 2022 10:58:17 GMT

Hate to be the guy that states the error codes:

Error "Expressions that yield variant data-type annot be used to define calculated columns.

Am I on the right path? Or was it a mistake to use variables in a calculated columns to begin with.

Re: Calculated columns that outputs the difference in workdays shall only run if there are 2 dates

tamerj1 — Thu, 02 Jun 2022 10:58:47 GMT

teflonreis
Just delete the , ""

Re: Calculated columns that outputs the difference in workdays shall only run if there are 2 dates

teflonreis — Thu, 02 Jun 2022 11:04:43 GMT

Oh that fixed it. Sorry for the stupid questions.

Many many thanks for the patience and help.