Create reusable date table with DAX

0
votes

I build a date table in Power BI with all possible combination of date columns. However, I was wondering if it is possible to write one DAX query that creates all these columns automatically.

How to combine the DAX logic from each individual column together so PowerBI creates all the columns at once.

My last try:

DateTable = 
VAR BaseCalendar =
CALENDAR(
    FIRSTDATE(column X[Date]),
    LASTDATE(column X[Date]))
RETURN
    GENERATE(
    BaseCalendar,
    VAR BaseDate = [Date]
    VAR YearDate = YEAR(BaseDate)
    VAR MonthNumber = MONTH(BaseDate)
    RETURN ROW(
        "FullYear", YEAR([Date]),
        "ShortYear", VALUE(Right(Year([Date]),2)),
        "MonthNumber", MONTH([Date]),
        "MonthNumberFull", FORMAT([Date], "MM"),
        "MonthFull", FORMAT([Date], "MMMM"),
        "MonthAbbr", FORMAT([Date], "MMM"),
        "WeekNumber", WEEKNUM([Date]),
        "WeekNumberFull", FORMAT(Weeknum([Date]), "00"),
        "QuarterFull", "Quarter " & ROUNDDOWN(MONTH([Date])/4,0)+1,
        "QuarterNumber", ROUNDDOWN(MONTH([Date])/4,0)+1,
        "QuarterAndYear", BaseCalendar[QuarterNumber] & " " & [FullYear],
        "YearMonthNum", VALUE(BaseCalendar[FullYear] & BaseCalendar[MonthNumberFull]),
        "Quarter", "Q" & ROUNDDOWN(MONTH([Date])/4,0)+1,
        "ISODate", BaseCalendar[FullYear] & BaseCalendar[MonthNumberFull] & BaseCalendar[DayOfMonthFull],
        "DayOfMonth", DAY(BaseCalendar[Date]),
        "DayOfMonthFull", FORMAT(DAY(BaseCalendar[Date]),"00"),
        "DayOfWeek", WEEKDAY(BaseCalendar[Date],2),
        "DayOfWeekFull", FORMAT(BaseCalendar[Date], "DDDD"),
        "DayOfWeekAbbr", FORMAT(BaseCalendar[Date], "DDD"),
        "DayOfWeekAbbr", FORMAT(BaseCalendar[Date], "DDD"),
        "QuarterAbbr", "Qtr" & ROUNDDOWN(MONTH(BaseCalendar[Date])/4,0)+1,
        "MonthAndYearAbbr", BaseCalendar[MonthNumberFull] & " " & BaseCalendar[FullYear],
        "QuarterAndYearNum", BaseCalendar[FullYear] & BaseCalendar[QuarterNumber],
        "QuarterAndYearNum", BaseCalendar[FullYear] & BaseCalendar[QuarterNumber]
    )
)

When I hover over the variables that are underlined in red it always says: Cannot find name: XYZ

I do understand that the code above is not working for several reasons. I am new to this and cannot think of a different approach.

My final goal is that I have a block of code I can copy paste when creating a new table that will automatically create all the columns I created by hand once.

Any Input or help is very much appreciated.

Many thanks in advance, MLU

2
automationpowerbivisualizationdax

2 Answers

1
votes

Everyone!

I ended up figuring it out. It is possible to reference to previously defined columns. However, I had to create variables that basically were the same as the query for the columns. See the code underneath for better understanding.

Outcome: The query does work perfectly fine. Only thing I need to do now is to copy paste the query when creating a new date table. The logic works and after setting the connection and referring to the new table as Datetable, the date table logic applies as it is supposed to do. 

enterDateTableXX = 
VAR BaseCalendar =
    CALENDAR(
        FIRSTDATE(Datatable XX[Date]),
        LASTDATE(Datatable XX[Date]))
RETURN
    GENERATE(
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR FullYear = YEAR(BaseDate)
        VAR MonthNumber = MONTH(BaseDate)
        VAR QuarterNumber = ROUNDDOWN(MONTH([Date])/4,0)+1
        VAR QuarterAndYear =  QuarterNumber & " " & FullYear
        VAR MonthNumberFull = FORMAT(BaseDate, "MM")
        VAR DayOfMonthFull = FORMAT(DAY(BaseDate),"00")
        RETURN ROW(
            "FullYear", YEAR(BaseDate),
            "ShortYear", VALUE(Right(Year(BaseDate),2)),
            "MonthNumber", MONTH(BaseDate),
            "MonthNumberFull", FORMAT(BaseDate, "MM"),
            "MonthFull", FORMAT(BaseDate, "MMMM"),
            "MonthAbbr", FORMAT(BaseDate, "MMM"),
            "WeekNumber", WEEKNUM(BaseDate),
            "WeekNumberFull", FORMAT(Weeknum(BaseDate), "00"),
            "QuarterFull", "Quarter " & ROUNDDOWN(MONTH(BaseDate)/4,0)+1,
            "QuarterNumber", ROUNDDOWN(MONTH(BaseDate)/4,0)+1,
            "QuarterAndYear", QuarterNumber & " " & FullYear,
            "YearMonthNum", VALUE(FullYear & MonthNumberFull),
            "Quarter", "Q" & ROUNDDOWN(MONTH([Date])/4,0)+1,
            "ISODate", FullYear & MonthNumberFull & DayOfMonthFull,
            "DayOfMonth", DAY(BaseDate),
            "DayOfMonthFull", FORMAT(DAY(BaseDate),"00"),
            "DayOfWeek", WEEKDAY(BaseDate,2),
            "DayOfWeekFull", FORMAT(BaseDate, "DDDD"),
            "DayOfWeekAbbr", FORMAT(BaseDate, "DDD"),
            "QuarterAbbr", "Qtr" & ROUNDDOWN(MONTH(BaseDate)/4,0)+1,
            "MonthAndYearAbbr", MonthNumberFull & " " & FullYear,
            "QuarterAndYearNum", FullYear & QuarterNumber
        )
    )
0
votes

You should use ADDCOLUMNS, something like this

Date =
ADDCOLUMNS (
    CALENDAR ( FIRSTDATE(column X[Date]); LASTDATE(column X[Date]) );
    "Quarter"; "Q" & FORMAT ( [Date]; "Q" )
    ...
    ...
)

You cannot reference a name from a column you added in another added column. So this would fail:

Date =
ADDCOLUMNS (
    CALENDAR ( FIRSTDATE(column X[Date]); LASTDATE(column X[Date]) );
    "Quarter"; "Q" & FORMAT ( [Date]; "Q" );
    "Year-Quarter"; FORMAT([Date];"yyyy") & " - " & [Quarter] // < would fail 
)

You can only reference Date so to get Year-Quarter:

Date =
ADDCOLUMNS (
    CALENDAR ( FIRSTDATE(column X[Date]); LASTDATE(column X[Date]) );
    "Quarter"; "Q" & FORMAT ( [Date]; "Q" );
    "Year-Quarter"; FORMAT([Date];"yyyy") & " - Q" & FORMAT ( [Date]; "Q" )       
)