Problems with dates in visuals

[mterry]

I have merged a few tables and have data from the beginning of 2015 through YTD 2018. The dates are formatted as dates, and when I sort them I can see that they start from the correct date (1/1/2015) and go through the correct date (7/18/2018 as of today). However, when I go to create a visual, be that a chart or table, and use the dates on the axis or rows, it's showing me years up to 2803? I'm wonder where this disconnect can be? This is sensitive data so unfortunately I'm unable to post, just wonder if there are any general suggestions as to why the date data would be correct in the query editor and table view, but not be coming through accurately in my visuals?

[Anonymous · Accepted solution]

You should use a Calendar table. That normalizes the date data. The Date pulled into the Visuals is from the Calendar Table. 

Connect the Calendar table to the primary Data Tables. The Data tables should not be connected to each other (most mlikely will not connect). the Calendar table is the common link in the case of using dates. 

That will not clean up bad date data.

Here is the code for a Calendar Table I use (change the name in the front to your chosen name but don't use a DAX command name:

Dates_INV = GENERATE (

    CALENDAR( DATE( YEAR( TODAY() ) - 3, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),

    VAR startOfWeek = 2 // Where 1 is Sunday so this returns Monday as Week Start   

    VAR currentDay = [Date]

    VAR days = DAY( currentDay )

    VAR months = MONTH ( currentDay )

    VAR years = YEAR ( currentDay )

    VAR nowYear = YEAR( TODAY() )

    VAR nowMonth = MONTH( TODAY() )

    VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1

    VAR todayNum = WEEKDAY( TODAY() )

    VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )

  RETURN ROW (

    "day", days,

    "month", months,

"MonthNameShort", FORMAT ( months, "mmm" ),

"MonthNameLong", FORMAT ( months, "mmmm" ),

    "year", years,

    "day index", dayIndex,

    "week index", weekIndex,

    "month index", INT( (years - nowYear ) * 12 + months - nowMonth ),

    "year index", INT( years - nowYear )

)

))

[Anonymous]

OH. When connecting, the Cross Filter dicection is Single and the arrow in the graphical schema should flow from the Calendar table (the table with one point) to the data tables with multiple points (multiple dates).

[Seward12533]

Unless its somthign like the data if formatted for the wrong kind of data? YYYYMMDD instead of MMDDYY

Or, I'm assuming that is the size of your date table and you have a measure that is calcualting a non blank (non Null) result.  If this is the case you can avoid this by limiting the dates either through Report, Page or Visual Filters. With Slicers to define date ranges or by using DAX in your measure to block results form dispalying.

Without more details of your data model and measures its hard to provide a specific solution. Can you dummy up some data so we get a better idea of what your trying to do?

[mterry]

I found I think 5 rows out of the 3MM that I think we're throwing this off - cleaning it up and checking to see if there is still an issue. 

[mterry]

Yeah let me see if I can remove some of the other data and keep only the dates, I'll try to post that.