Date Variations .[Month], .[Year] etc. in own Date Table

[trevrobwhite2]

I've created my own Date table and marked it as such plus created a date hierarchy (and remembered to sort the columns)

Global DateTable = 
ADDCOLUMNS (
    CALENDARAUTO(),
    "Year", YEAR([Date]),
    "MonthNumber", MONTH([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "Quarter", "Q" & QUARTER([Date]),
    "WeekNumber", WEEKNUM([Date]),
    "Week", "Week " & WEEKNUM([Date]),
    "Weekday", FORMAT([Date], "ddd"),
    "Day", FORMAT([Date], "d"),
    "IsBeforeToday", IF([Date] < TODAY(), 1, 0),
    "IsPreviousMonth", IF(
        [Date] < EOMONTH(TODAY(), -1) + 1,
        1,
        0
    )
)

One thing I'm missing over Time Intelligence, is I cannot access variants of the date for example accessing:

'DateTable'[Date].[Month] the .[Month] property isn't available and I get the error:

Column reference to 'Date' in table 'DateTable' cannot be used with a variation 'Month' because it does not have any.

I couldn't seem to find any documentation on how to associate my columns as variants of the Date field, this is useful when I'm calcuating previous fields in my matrix because I do the following:

VAR previousSpend = 
    SWITCH(
        TRUE(),
        ISINSCOPE('DateTable'[Date].[Month]),
            CALCULATE(
                SUM(usage[true_cost]),
                PREVIOUSMONTH('DateTable'[Date]),
                REMOVEFILTERS('DateTable'[Date])
            ),
        ISINSCOPE('DateTable'[Date].[Quarter]),
            CALCULATE(
                SUM(usage[true_cost]),
                PREVIOUSQUARTER('DateTable'[Date]),
                REMOVEFILTERS('DateTable'[Date])
            ),
        ISINSCOPE('DateTable'[Date].[Year]),
            CALCULATE(
                SUM(usage[true_cost]),
                PREVIOUSYEAR('DateTable'[Date]),
                REMOVEFILTERS('DateTable'[Date])
            )
    )

RETURN
IF (currentSpend = 0, BLANK(), (previousSpend - currentSpend))

Rather than calling REMOVEFILTERS on every field in my date hierachy.

Any ideas or am I approaching this wrong?

[rohit1991 · Accepted solution]

Hi @trevrobwhite2 .

The .Month and .Year variations are only available for built-in date hierarchies, which do not auto-generate in custom Date tables.

Fix: Manually Create a Date Hierarchy

1. In Model View, right-click the 'Date' column → Create Hierarchy.
2. Add Year, Quarter, Month, Week, Day columns to the hierarchy.
3. Use ISINSCOPE('DateTable'[Year]) instead of .Year in calculations.

Alternative Fix: Use a Built-in Date Table

• Enable Auto Date/Time in File → Options → Data Load.
• Use 'DateTable'[Date] instead of manually defining variants.

[Anonymous]

Hi @trevrobwhite2 ,

May I ask if you have gotten this issue resolved?
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster .
Thank you very much for your kind cooperation!

Best Regards,
Dengliang Li

[johnt75 · Accepted solution]

Rather than referring to [Date].[Month] you can use the 'Date'[Month] column in your code and your visuals, although you may want to add a year month column to the date table and use that, so that e.g. January 2024 can be distinguished from January 2025.

Also, when you are manipulating filters on the date column of a table marked as the date table you don't need to use REMOVEFILTERS explicitly, that is handled automatically. You could rewrite your code as 

VAR previousSpend =
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'DateTable'[Month] ), CALCULATE ( SUM ( usage[true_cost] ), PREVIOUSMONTH ( 'DateTable'[Date] ) ),
        ISINSCOPE ( 'DateTable'[Quarter] ), CALCULATE ( SUM ( usage[true_cost] ), PREVIOUSQUARTER ( 'DateTable'[Date] ) ),
        ISINSCOPE ( 'DateTable'[Year] ), CALCULATE ( SUM ( usage[true_cost] ), PREVIOUSYEAR ( 'DateTable'[Date] ) )
    )
RETURN
    IF ( currentSpend = 0, BLANK (), ( previousSpend - currentSpend ) )

[rohit1991 · Accepted solution]

Hi @trevrobwhite2 .

The .Month and .Year variations are only available for built-in date hierarchies, which do not auto-generate in custom Date tables.

Fix: Manually Create a Date Hierarchy

1. In Model View, right-click the 'Date' column → Create Hierarchy.
2. Add Year, Quarter, Month, Week, Day columns to the hierarchy.
3. Use ISINSCOPE('DateTable'[Year]) instead of .Year in calculations.

Alternative Fix: Use a Built-in Date Table

• Enable Auto Date/Time in File → Options → Data Load.
• Use 'DateTable'[Date] instead of manually defining variants.