Do not understand why I'm getting "Couldn't load the data for this visual" error

[hsmit]

This works:

Anniversary Date =

var IsCurrentYearLeapYear = IF(MOD(YEAR(TODAY()), 400) = 0, "Yes", IF(MOD(YEAR(TODAY()), 4) = 0 && MOD(YEAR(TODAY()), 100) <> 0, "Yes", "No"))

var IsSeniorityDateLeapDay = IF(MONTH('Employee Title'[Seniority Date]) = 2 && DAY('Employee Title'[Seniority Date]) = 29, "Yes", "No")

return (IF(IsCurrentYearLeapYear = "No" && IsSeniorityDateLeapDay = "Yes",

DATE(YEAR(TODAY()), 3, 1),

DATE(YEAR(TODAY()), MONTH('Employee Title'[Seniority Date]), DAY('Employee Title'[Seniority Date]))))

But this doesn't, and it makes it very hard to verify expected results withing the computed column.

Anniversary Date =

var IsCurrentYearLeapYear = IF(MOD(2020, 400) = 0, "Yes", IF(MOD(2020, 4) = 0 && MOD(2020, 100) <> 0, "Yes", "No"))

var IsSeniorityDateLeapDay = IF(MONTH('Employee Title'[Seniority Date]) = 2 && DAY('Employee Title'[Seniority Date]) = 29, "Yes", "No")

return (IF(IsCurrentYearLeapYear = "No" && IsSeniorityDateLeapDay = "Yes",

DATE(YEAR(TODAY()), 3, 1),

DATE(YEAR(TODAY()), MONTH('Employee Title'[Seniority Date]), DAY('Employee Title'[Seniority Date]))))

I get:

Couldn't load the data for this visual
Cannot construct data type date, some of the arguments have values which are not valid.. The exception was raised by the IDataReader interface.

But, If I create a column using the expression I can swap out "YEAR(TODAY())" and "2020" it works just fine
IsCurrentYearLeapYear = IF(MOD(2020, 400) = 0, "Yes", IF(MOD(2020, 4) = 0 && MOD(2020, 100) <> 0, "Yes", "No"))

Of course this would all be moot if there was a built in funtion to determine a Leap Year for a given date

[Anonymous]

@hsmit,

I make a test using the above two DAX in Power BI Desktop November, both work as expected. Could you please share the sample data in Seniority Date so that I can test?


Regards,
Lydia

[hsmit]

@Lydia

I can't give you the data but I can show you the database row with headers.

LOCATION OFFICELOCATION SENIORITYDATE SENIORITYDATETZID TITLE VALIDFROM VALIDFROMTZID VALIDTO VALIDTOTZID WORKER WORKSFROMHOME ORIGINALHIREDATETIME ORIGINALHIREDATETIMETZID ANNIVERSARYDATETIME ANNIVERSARYDATETIMETZID PARTITION RECID RECVERSION MODIFIEDDATETIME MODIFIEDBY CREATEDDATETIME CREATEDBY
0  2008-02-29 06:00:00.000 21002 0 2018-11-29 17:17:36.000 21002 2154-12-31 23:59:59.000 21002 68719480106 0 1900-01-01 00:00:00.000 0 1900-01-01 00:00:00.000 0 5637144576 68719479518 1597902769 2018-11-30 01:41:16.000 Admin 2018-11-29 17:18:01.000 Admin

OFFICELOCATION value is a single space (NVARCHAR)

 

[Anonymous]

@hsmit,

I am unable to reproduce this issue using the above single row. Could you please copy all the sample data of your table in a Excel file and share Excel file here? 

Regards,
Lydia