topic Re: Best Practice for Creating Optimized Date Dimension Table (Including Fiscal Year: April–March) Post in DAX Commands and Tips

Best Practice for Creating Optimized Date Dimension Table (Including Fiscal Year: April–March) Post

GanesaMoorthyGM — Wed, 23 Jul 2025 10:49:22 GMT

Hi all,

I'm working on building a Date Dimension table for my Power BI model and would appreciate your suggestions on the best practices and an optimized query to generate it.

Here's what I'm looking for in the Date table:

Date
Day
Month Number
Month Name
Month Order (for sorting)
Quarter
Quarter Name
Year
Fiscal Year
Fiscal Quarter
Week Number
Day of Week / Weekday Name

👉 Important Note: Our Fiscal Year starts in April and ends in March.

I’m aiming to these for performance and scalability are important, so an optimized approach would be ideal.

If anyone has a tried-and-tested query (especially one handling fiscal logic cleanly), or tips on calculated columns/transformations for this, please share. It would be really helpful for me and others facing the same use case.

Thanks in advance!

Re: Best Practice for Creating Optimized Date Dimension Table (Including Fiscal Year: April–March) Post

mdaatifraza5556 — Wed, 23 Jul 2025 11:15:40 GMT

Hi GanesaMoorthyGM

We can create using Power Query.

Below M code
Dynamically generating Date Dimension table based on the min and max dates from Table

let
// Reference your table
Source = Table, // Name of the table from where min and max date to be taken.

// Get the minimum and maximum dates from your fact table's date column
MinDate = List.Min(Source[OrderDate]),
MaxDate = List.Max(Source[OrderDate]),

// Generate date range list
DateList = List.Dates(MinDate, Duration.Days(MaxDate - MinDate) + 1, #duration(1, 0, 0, 0)),
DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),

// Adding Columns
AddDay = Table.AddColumn(DateTable, "Day", each Date.Day([Date]), Int64.Type),
AddMonthNum = Table.AddColumn(AddDay, "Month Number", each Date.Month([Date]), Int64.Type),
AddMonthName = Table.AddColumn(AddMonthNum, "Month Name", each Date.ToText([Date], "MMMM"), type text),
AddMonthOrder = Table.AddColumn(AddMonthName, "Month Order", each Date.Month([Date]), Int64.Type),
AddQuarter = Table.AddColumn(AddMonthOrder, "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
AddQuarterName = Table.AddColumn(AddQuarter, "Quarter Name", each "Q" & Number.ToText(Date.QuarterOfYear([Date])), type text),
AddYear = Table.AddColumn(AddQuarterName, "Year", each Date.Year([Date]), Int64.Type),
AddWeekNum = Table.AddColumn(AddYear, "Week Number", each Date.WeekOfYear([Date]), Int64.Type),
AddDayOfWeek = Table.AddColumn(AddWeekNum, "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
AddWeekdayName = Table.AddColumn(AddDayOfWeek, "Weekday Name", each Date.ToText([Date], "dddd"), type text),

// Fiscal Year (April to March)
AddFiscalYear = Table.AddColumn(AddWeekdayName, "Fiscal Year", each
let
year = Date.Year([Date]),
month = Date.Month([Date])
in
if month >= 4 then Text.From(year) & "-" & Text.End(Text.From(year + 1), 2)
else Text.From(year - 1) & "-" & Text.End(Text.From(year), 2),
type text),

AddFiscalQuarter = Table.AddColumn(AddFiscalYear, "Fiscal Quarter", each
let
month = Date.Month([Date])
in
if month >= 4 and month <= 6 then "Q1"
else if month >= 7 and month <= 9 then "Q2"
else if month >= 10 and month <= 12 then "Q3"
else "Q4", type text)

in
AddFiscalQuarter

If this answers your questions, kindly accept it as a solution and give kudos

Re: Best Practice for Creating Optimized Date Dimension Table (Including Fiscal Year: April–March) Post

GanesaMoorthyGM — Wed, 23 Jul 2025 11:26:09 GMT

Thanks for your quick response mdaatifraza5556.
I'll definitely give a try to this and will update here.
Thanks Again!

Re: Best Practice for Creating Optimized Date Dimension Table (Including Fiscal Year: April–March) Post

Anonymous — Thu, 24 Jul 2025 11:09:54 GMT

Hi GanesaMoorthyGM,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are building a Date Dimension table for a Power BI model and is looking for best practices and an optimized Power Query (M) script to generate it. As MasonMA and mdaatifraza5556 both responded to your query, kindly go through their responses and check if the issue can be resolved.

I would also take a moment to thank MasonMA and mdaatifraza5556, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.

Best Regards,
Hammad.