can i add a new attribute as column in date dimension for year in existing model in desktop?

[Atl]

Hello everyone,

can i add a new attribute as column in date dimension for year in existing model in desktop? Existing model has incremental refresh setup and already being used for multpiple dashboards/reports in power bi service.

Thank you in advance.

[AnalyticsWizard · Accepted solution]

@Atl 

Certainly! Adding a new attribute (column) for the year in an existing date dimension within your Power BI model is a common task. Let’s walk through the steps:

1. Creating a Date Dimension:

   • If you don’t already have a date dimension table, consider creating one. A date dimension helps organize and analyze time-related data effectively.
   • You can create a date dimension using Power Query (M language) or DAX (Data Analysis Expressions). Both approaches have their advantages:
     • Power Query allows fetching data from live web APIs (e.g., public holidays), which can be beneficial.
     • DAX is often used for calculated columns within the Data Model.

2. Creating a Date Dimension Using Power Query:

   • Open your Power BI Desktop file.
   • Go to Get Data ➡️ Blank Query. This creates an empty query.
   • In the blank query, you’ll build your date dimension from scratch.
   • Determine the start year and end year for your date dimension.
   • Create columns for calendar dates (e.g., day, month, year) using Power Query transformations.
   • Fetch public holidays (if needed) using live web APIs.
   • Configure your date dimension with start and end dates.

3. Adding a Year Attribute:

   • Once you have your date dimension, you can add a year attribute to it.
   • In the Power Query Editor, create a new column using the following DAX expression:

     Year = YEAR([Date])

   • This expression calculates the year based on the existing date column.

4. Additional Attributes:

   • You can further enhance your date dimension by adding more attributes (columns):
     • Day: Use Format([Date], "DDDD") to get the day of the week.
     • Quarter: Extract the quarter information if needed.

5. Model View and Hierarchy:

   • In the Power BI Desktop, switch to the Model View.
   • Right-click on the Year column (field) and create a hierarchy (if desired).
   • You can also create custom hierarchies by combining different date attributes (e.g., year, quarter, month).

6. Incremental Refresh Considerations:

   • Since your existing model has incremental refresh set up, ensure that any changes you make to the date dimension won’t disrupt the incremental refresh process.
   • Test the impact of adding the new year attribute on your existing dashboards/reports.

For more detailed steps and examples, you can refer to resources like the RADACAD blog post on creating a date dimension or explore other Power BI tutorials123.

[Atl]

Thank you