Null Values showing when creating date table - source data is not null

[jbrooks91]

Hi All,

I've created a date table in my schema in order to do some different "slicing and dicing" but for some reason it doesn't seem to be picking up "new" dates in my table. Even when I hit "refresh" I will still end up with some null values. Any idea why?

Date Table:

Date =

VAR MinDate =

    DATE( YEAR( MIN( 'Survey'[responsedate] ) ), MONTH( MIN( 'Survey'[responsedate] ) ), 1 )

VAR MaxDate =

    DATE( YEAR( MAX( 'Survey'[responsedate] ) ), MONTH( MAX( 'Survey'[responsedate] ) ), 1 )

VAR DateRange =

    CALENDAR( MinDate, MaxDate )

RETURN

ADDCOLUMNS(

    DateRange,

    "Year", YEAR( [Date] ),

    "MonthNumber", MONTH( [Date] ),

    "MonthName", FORMAT( [Date], "MMMM" ),

    "MonthYear", FORMAT( [Date], "MMM YYYY" ),

    "Quarter", "Q" & FORMAT( [Date], "Q" ),

    "YearMonthNumber", YEAR( [Date] ) * 100 + MONTH( [Date] ), // Useful for sorting

    "MonthIndex", DATEDIFF( MinDate, [Date], MONTH ) + 1

)

[Anonymous · Accepted solution]

Hi @jbrooks91 

Thank you very much FarhanJeelani for your prompt reply.

Try this:

Date = 
VAR MinDate =
    MINX(FILTER('Survey', NOT(ISBLANK('Survey'[responsedate]))), 'Survey'[responsedate])
VAR MaxDate =
    MAXX(FILTER('Survey', NOT(ISBLANK('Survey'[responsedate]))), 'Survey'[responsedate])
VAR DateRange =
    CALENDAR(MinDate, MaxDate)
RETURN
ADDCOLUMNS(
    DateRange,
    "Year", YEAR([Date]),
    "MonthNumber", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "MonthYear", FORMAT([Date], "MMM YYYY"),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "YearMonthNumber", YEAR([Date]) * 100 + MONTH([Date]), // Useful for sorting
    "MonthIndex", DATEDIFF(MinDate, [Date], MONTH) + 1
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FarhanJeelani]

Hi @jbrooks91 , It looks like your date table might not be capturing the full range of dates, leading to null values. A few key points to check:

MaxDate Issue:

Your Maxdate is currently set to the start of the month (DATE(YEAR(MAX('Survey'[responsedate])), MONTH(MAX('Survey'[responsedate])), 1)), which might exclude dates after the first day of the last month.

Fix: Use EOMONTH to capture the full month:

DAX

VAR MaxDate = EOMONTH(MAX('Survey'[responsedate]), 0

Null Values in the visual:

Ensure that the relationship between your Date table and the Survey table is a one-to-many 

(Date[Date] -> Survey[responsedate]).

In your visual settings, uncheck "Show items with no data" under the Fields pane if it is selected.

Please mark this as solution if it helps you. Appreciate Kudos.