trouble creating a date hierarchy in Power BI

[Victoria_Rios]

I'm having trouble creating a date hierarchy in Power BI. I've created a calculated column named 'FechaEntregaCalculada' using the following DAX formula:

FechaEntregaCalculada = DATE(

    YEAR('Archivo de prueba'[Fecha Entrega]),

    MONTH('Archivo de prueba'[Fecha Entrega]),

    DAY('Archivo de prueba'[Fecha Entrega])

)

Despite this, when I right-click on the column and try to create a hierarchy, the option isn't available.

I've already tried the following:

• Changing the data type: Ensured the calculated column is formatted as a date.
• Renaming the column: Used a more descriptive name like 'Calculated Delivery Date'.
• Checking relationships: Verified that the relationships between my tables are correct.
• Refreshing the model: Tried refreshing the model to see if that would resolve the issue.

I'm not sure what else to try. Any suggestions would be greatly appreciated.

Here are some additional details that might be helpful:

• Data source: [my  data source Excel]
• Data format: [fecha]
• Power BI version: [my  Power BI version is : 2.131.1203.0 64-bit (julio de 2024)]

[AmiraBedh · Accepted solution]

Try to create a calculated table as your date dimension :

DateDimension = 
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "Quarter", QUARTER([Date]),
    "Day", DAY([Date]),
    "DayOfWeek", WEEKDAY([Date]),
    "WeekDayName", FORMAT([Date], "dddd"),
    "IsWeekend", IF(WEEKDAY([Date], 2) > 5, TRUE, FALSE)
)

Link it to your table (consider that it is a fact) and then use the columns from DateDimension and create the hierarchy you want. Don't forget to mark the DateDimension as Date table.

[lbendlin · Accepted solution]

There is no need to do this in Power Query or in DAX. Dates are generally immutable.  Create an external reference table that has everything precomputed.

[AmiraBedh · Accepted solution]

Try to create a calculated table as your date dimension :

DateDimension = 
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "Quarter", QUARTER([Date]),
    "Day", DAY([Date]),
    "DayOfWeek", WEEKDAY([Date]),
    "WeekDayName", FORMAT([Date], "dddd"),
    "IsWeekend", IF(WEEKDAY([Date], 2) > 5, TRUE, FALSE)
)

Link it to your table (consider that it is a fact) and then use the columns from DateDimension and create the hierarchy you want. Don't forget to mark the DateDimension as Date table.