Fabric Date Hierarchy Accelerator

Costa Rica

brown9804

Last updated: 2025-07-17

---

Power BI currently does not support built-in date hierarchies in DirectQuery or Direct Lake modes1. This means the usual auto-generated hierarchy (Year > Quarter > Month > Day) won’t appear by default. Automating date hierarchies in Power BI and Microsoft Fabric eliminates repetitive manual work, ensures consistency, and accelerates report development. Leveraging Direct Lake improves report performance and enables real-time analytics. This toolkit provides scripts, templates, and configuration files to standardize and automate hierarchy creation, making it easier for teams to deliver high-quality, performant analytics solutions.

Important

Please note that these demos are intended as a guide and are based on my personal experiences. For official guidance, support, or more detailed information, please refer to Microsoft's official documentation or contact Microsoft directly: Microsoft Sales and Support

List of References (Click to expand)

• Leveraging pure Direct Lake mode for maximum query performance
• Dataflow Gen2 data destinations and managed settings
• Notebook source control and deployment
• Data types in Fabric Data Warehouse

Table of Contents (Click to expand)

• Prerequisites
• Automation Walkthrough
• Best Practices

Prerequisites

Depending on the approach you take, you may use some, all, or none of these prerequisites.

• Power BI Desktop
• Node.js (for running review scripts)
• Git (for cloning the repository)
• Install Chocolatey (One-Time Setup)

  1. Open PowerShell as Administrator

  2. Run this command:

     Set-ExecutionPolicy Bypass -Scope Process -Force; `
     [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; `
     iex ((New-Object System.Net.WebClient).DownloadString('https://community.chocolatey.org/install.ps1'))

  3. After installation, restart your terminal and run:

     choco install make

     Install.choco.and.make.mp4

Automation Walkthrough

1. Prepare Your Workspace: Install prerequisites, and clone this repository.
2. Ensure you have access to your Microsoft Fabric workspace.
3. Option 1: A parameterized Power Query script that generates a reusable date table. It supports fiscal logic, holidays, and time intelligence (YTD, MTD, etc.), and stores the result in a Lakehouse for Direct Lake consumption.
4. Option 2: For those who prefer a code-first approach, this option uses Fabric notebooks to generate the date table and write it to a Lakehouse. It’s ideal for advanced logic and pipeline integration.
5. Option 3: A semantic model template that includes preconfigured hierarchies and measures. This is useful for teams looking to standardize reporting and accelerate development.

Best Practices

• Version Control: If you are using a notebook, connect your Fabric workspace to GitHub or Azure DevOps to enable version control for the notebook.
• Schedule/Automate: Use Fabric Data Pipelines to schedule the notebook execution (e.g., daily, weekly).
• Consume in Power BI or Other Fabric Workloads: Use the generated Lakehouse table as a source in Power BI datasets, Data Warehouses, or other Fabric experiences.
• CI/CD Integration: Use Fabric’s Git integration for code review, pull requests, and automated deployment.

Refresh Date: 2025-07-17