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 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
)
)
Solved! Go to Solution.
Hi @teflonreis
Replace
AND(Table[date1]<>0, Table[date2]<>0)
with
AND(Table[date1 > 0, Table[date2] > 0)
Hi @teflonreis
Replace
AND(Table[date1]<>0, Table[date2]<>0)
with
AND(Table[date1 > 0, Table[date2] > 0)
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.
Oh that fixed it. Sorry for the stupid questions.
Many many thanks for the patience and help.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |