Hello community,
I am facing an error with a calculated column when it comes to date:
c_AnniversaryDate UDP =
VAR six_months =
DATE ( YEAR ( [Current year start date UDP] ), MONTH ( dw_tbld_chassis[ContractStartDate] ) + 6, DAY (dw_tbld_chassis[ContractStartDate] ) )
VAR twelve_months =
DATE ( YEAR ( [Current year start date UDP] ) + 1, MONTH ( dw_tbld_chassis[ContractStartDate] ), DAY ( dw_tbld_chassis[ContractStartDate] ) )
RETURN
IF ( [Current year start date UDP] < TODAY ()
&& TODAY () > six_months,
twelve_months,
six_months
)
"An argument of function 'Date' has the wrong data type or the result is too large or too small.
I have been researching and seems many had the same problem, what's curious about my case is that before I had another column with the same definition, and never had any problem (I am repluging a Power BI report from an on-premise database to Databricks).
The measures and columns that this calculated column is using are the following ones:
Current year start date UDP =
IF (
(
ISFILTERED ( dw_tbld_chassis[FleetID] )
|| ISFILTERED ( dw_tbld_chassis[ContractCustomerName] )
)
&& COUNTROWS ( VALUES ( dw_tbld_chassis[FleetID] ) ) > 1,
"More than one value",
IF (
(
ISFILTERED ( dw_tbld_chassis[FleetID] )
|| ISFILTERED ( dw_tbld_chassis[ContractCustomerName] )
),
MIN ( dw_tbld_chassis[c_CurrentYearStartDate UDP] ) + 1,
"No selection made"
)
)
c_CurrentYearStartDate UDP =
IF (
DATE ( YEAR ( dw_tbld_chassis[ContractStartDate] ) + dw_tbld_chassis[c_YearsBetweenContractStartDateAndTodayYear UDP], MONTH ( dw_tbld_chassis[ContractStartDate] ), DAY ( dw_tbld_chassis[ContractStartDate] ) )
< TODAY (),
DATE ( YEAR ( dw_tbld_chassis[ContractStartDate] ) + dw_tbld_chassis[c_YearsBetweenContractStartDateAndTodayYear UDP], MONTH ( dw_tbld_chassis[ContractStartDate] ), DAY ( dw_tbld_chassis[ContractStartDate] ) ),
DATE ( YEAR ( dw_tbld_chassis[ContractStartDate] ) + ( dw_tbld_chassis[c_YearsBetweenContractStartDateAndTodayYear UDP] ) - 1, MONTH ( dw_tbld_chassis[ContractStartDate] ), DAY ( dw_tbld_chassis[ContractStartDate] ) )
)
c_YearsBetweenContractStartDateAndTodayYear UDP = DATEDIFF(dw_tbld_chassis[ContractStartDate], TODAY(), YEAR)
Basically what I have changed are the source tables and columns from the on-prem database to databricks. Everything was working before, it's just copy-pasting formulas, I also checked that the data types are the same.
Hope it makes sense 🙂
Thanks in advance for any help
