Business Central + Power BI: Making Sense of Dates

Generic date tables are fine for ops dashboards. For finance, you want Posting Date sliced by BC Accounting Periods so month/quarter/year totals match what finance expects.

Below is a simple pattern:

1. pull Accounting Periods from BC,

2. compute an End Date for each period,

3. generate a daily Date table,

4. map each Date to its fiscal period,

5. relate Date → your facts (e.g., G/L Entry, Sales).

1) Get Accounting Periods from BC (Power Query M)

let
   // 1) Pull periods (OData V4 page "AccountingPeriods")
    PeriodsRaw =
        OData.Feed(
            "https://api.businesscentral.dynamics.com/v2.0/{tenant}/Production/ODataV4/Company('CRONUS USA, Inc.')/AccountingPeriods",
            null, [Implementation="2.0"]),

    // Keep the fields we need (names may vary slightly in your tenant)
    Periods =
        Table.SelectColumns(
            PeriodsRaw,
            {"Starting_Date", "Name", "Period_No", "Fiscal_Year", "Closed"}
        ),
    // Order by start date
    PeriodsSorted = Table.Sort(Periods, {{"Starting_Date", Order.Ascending}}),
    // 2) Add an End Date: the day before the next period starts
    WithIndex = Table.AddIndexColumn(PeriodsSorted, "idx", 0, 1, Int64.Type),
    NextStart = Table.AddColumn(
        WithIndex,
        "NextStart",
        each try WithIndex[Starting_Date]{[idx]+1} otherwise null,
        type date
    ),
    WithEndDate = Table.AddColumn(
        NextStart,
        "End_Date",
        each if [NextStart] <> null then Date.AddDays([NextStart], -1) else Date.AddDays([Starting_Date], 40), // last period: make wide; we’ll trim later
        type date
    ),
    PeriodsFinal = Table.RemoveColumns(WithEndDate, {"idx", "NextStart"})
in
    PeriodsFinal

Tip: If your field names differ (e.g., Starting Date with a space), adjust them in Table.SelectColumns.

2) Build a daily Date table and map to periods (Power Query M)

let
    // Assume you referenced the previous query as PeriodsFinal
    StartDate = List.Min(PeriodsFinal[Starting_Date]),
    EndDate   = List.Max(PeriodsFinal[End_Date]),

    // 3) Build the daily date table
    DateList  = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)),
    DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),

    // Add standard calendar columns (keep it light)
    AddYear   = Table.AddColumn(DateTable, "Year", each Date.Year([Date]), Int32.Type),
    AddMonthN = Table.AddColumn(AddYear, "MonthNo", each Date.Month([Date]), Int32.Type),
    AddMonthS = Table.AddColumn(AddMonthN, "Month", each Date.ToText([Date], "MMM"), type text),
    AddQM     = Table.AddColumn(AddMonthS, "Quarter", each "Q" & Number.ToText(Date.QuarterOfYear([Date])), type text),

    // 4) Map each date to its fiscal period (range match)
    // Do a Cartesian merge then filter—efficient enough for 10 years of days.
    JoinAll   = Table.AddColumn(AddQM, "tmp", each PeriodsFinal),
    Expanded  = Table.ExpandTableColumn(
                   JoinAll, "tmp",
        {"Starting_Date","End_Date","Name","Period_No","Fiscal_Year","Closed"},
                   {"PeriodStart","PeriodEnd","FiscalPeriodName","FiscalPeriodNo","FiscalYear","PeriodClosed"}),

    // Keep only rows where Date is within the period
    InRange   = Table.SelectRows(Expanded, each [Date] >= [PeriodStart] and [Date] <= [PeriodEnd]),

    // Remove helper columns
    Cleaned   = Table.RemoveColumns(InRange, {"PeriodStart","PeriodEnd"})
in
    Cleaned

You now have a Date dimension that carries FiscalYear, FiscalPeriodNo, and FiscalPeriodName exactly as defined in Business Central.

Prefer DAX for your Date table? You can still enrich it with the periods table in Power Query, then relate DAX Date to the enriched table. Keeping it all in Power Query is simpler for this pattern.

3) Model the relationships

• Relate Date[Date] → G/L Entry[Posting Date] (and any other fact’s posting date).

• Use Date[FiscalYear], Date[FiscalPeriodNo], Date[FiscalPeriodName] for slicers.

• If you need Document Date or Due Date for ops/AP/AR views, write measures with USERELATIONSHIP to toggle:

Amount by Document Date := CALCULATE (
    SUM ( 'Sales Invoice Line'[Amount] ),
    USERELATIONSHIP ( 'Sales Invoice Header'[Document Date], 'Date'[Date] ))

4) Multi-company calendars (optional)

If different companies in BC use different fiscal calendars:

• Pull AccountingPeriods per company (add a “Company” column to the periods query).

• Build a Date table per company or keep one Date table and a separate Periods table with a Company column, then use a slicer for Company to filter the active fiscal attributes.

Wrap up

• Finance expects BC Accounting Periods; give them a Date table that inherits those periods.

• Generate End Dates, map each day to a period, and relate on Posting Date.

• Use USERELATIONSHIP for alt date logics (Document/Due).

• Handle multi-company calendars by tagging periods per company.