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

[GanesaMoorthyGM]

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!

---

[v-mdharahman]

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.

[mdaatifraza5556 · Accepted solution]

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

[GanesaMoorthyGM]

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