Forum Discussion

GanesaMoorthyGM's avatar
GanesaMoorthyGM
Helper II
1 year ago
Solved

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

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!

"DAX " "DAX help" 'Need help' 'DAX'
"DAX Commands and Tips"
  • mdaatifraza5556's avatar
    mdaatifraza5556
    1 year ago

    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

3 Replies

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Super User
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.