Custom Date Table being corrupted after recent updates

[Anonymous]

I have a date table that is generated using the following DAX listed below.  This Date table joins together lots of other tables with Dates in it, and has functioned fantastic for the past two years.  At first I made my own hierarchy using the created columns, and then I used the built-in Date Hierarchy off the "Date Colum" itself. Worked great.  Until today... today for some reason the moment I map in a new table of data, Power BI then fails to recognize that the Date Table, Date column is a DATE any longer... and I have tried changing the column data type from Date/Time to Date and back, changing the Date format shown, and everything else I can think of.  No matter what, it is breaking my Calculated Measures, Date charts and visuals, and the Date Hierarchy is gone. 

I tried setting it as the Date Table, and designating the Date Column as the default Date, and turning off automatic date table creation in the options... nothing. I have tried undoing that. I have tried going back to a previous build or save where it worked correctly, no matter what I end up losing functionality and there isnt a way to get it back it seems.

What is going on?

DATE Table = ADDCOLUMNS( CALENDAR( DATE( 1980 , 01 , 01 ) , DATE( 2070 , 12 , 31 )) ,
"DateAsInteger" , FORMAT([Date] ,"YYYYMMDD" ),
"Year", YEAR([Date] ),
"MonthNumber", FORMAT([Date], "MM" ),
"WeekNumber" , WEEKNUM([Date],1 ),
"DayofMonthNumber", DAY([Date]),
"YearMonthnumber", FORMAT([Date], "YYYY-MM" ),
"MonthShortYear", FORMAT([Date], "mmm YYYY" ),
"MonthNameShort", FORMAT([Date], "mmm" ),
"MonthNameLong", FORMAT([Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY([Date] ),
"DayOfWeek", FORMAT([Date], "dddd" ),
"DayOfWeekShort", FORMAT([Date], "ddd" ),
"Quarter", "Q" & FORMAT([Date], "Q" ),
"Year Frac",  YEARFRAC(DATE(YEAR([Date]),01,01),[Date],1),
"DayofYear", DATEDIFF((DATE(YEAR([Date]),01,01)) , [Date],DAY) +1 ,
"YearQuarter", "Q" & FORMAT([Date], "Q YYYY" ) ,
"Year_Current", IF(YEAR([Date])=YEAR(TODAY()),"Y","N") ,
"Month_Current", IF( (MONTH([Date]) = MONTH(TODAY()) ) && (YEAR([Date])=YEAR(TODAY()) ) ,"Y","N") ,
"Year_Last", IF(YEAR([Date])=(YEAR(TODAY()) -1),"Y","N") ,
"Month_Last", IF( (MONTH([Date]) = (MONTH(TODAY()) -1) ) && (YEAR([Date])=YEAR(TODAY()) ) ,"Y","N")
)

[v-qiuyu-msft]

Hi @Anonymous,

Please ensure you turn on the "Show dates as a hierarchy" feature in desktop, then refresh the data again. 

Best Regards,
Qiuyun Yu 

[Anonymous]

@v-qiuyu-msft  Thanks, I already had that set as on.  I toggled it to off and restarted PBI, then back to on, then restarted PBI again. that doesn't improve the situation.

What did fix it was when I went in and deleted the only two tables that had a relationship between the Date Table Date value and the new table Date column that was in both directions for the cross-filter. As soon as I deleted that linked relationship it immediately recognized it as a Date again, and all hierarchy was reinstated and all measures worked accordingly again.

Strangely, after this - I then tested recreating that relationship again, and after doing it again - it maintained the date table recognition and functions properly.

So, it seems like there is some kind of strange issue at play with custom date tables that have a both-direction cross filter relationship after the most recent update, that requires you to delete and rebuild that relationship to restore proper date value functionality and recognition.