Power BI for Odoo：12 个生产就绪的 DAX 模式

We assume you have already pulled account_move, account_move_line, sale_order, sale_order_line, stock_quant, stock_move, res_partner, res_company, product_product, product_template, and account_account into a Power BI dataset. Keys are joined, currencies converted, and the date table is marked as a date table. From there, the patterns below are the difference between a data dump and a dashboard a CFO will trust.

Key Takeaways

• DAX time intelligence works on Odoo only when you build a proper Date table and mark it correctly — Odoo's create_date is a timestamp, not a date.
• Multi-company consolidation needs a Company dimension and explicit currency conversion measures, not a column.
• Account hierarchy in Odoo (parent_id self-reference) requires PATH() and PATHITEM() to flatten for Power BI.
• Cohort retention measures rely on EARLIER() or VAR ... CALCULATE patterns to capture the customer's first purchase.
• Inventory aging buckets are a SWITCH(TRUE()) over DATEDIFF from stock_quant.in_date.
• DAX measures should never reference Odoo IDs directly — always go through dimension tables for filter context.
• Calculation Groups (Power BI Desktop 2026-01) replace dozens of duplicate measures (PY, YoY, MTD, QTD).
• For 10M+ row Odoo deployments, switch to Import mode with Incremental Refresh, not DirectQuery.

---

Pattern 1: A Bullet-Proof Date Table

Odoo's account_move.date is a date, but create_date and write_date are timestamps with timezone. Mixing them in a date table causes silent filter mismatches. Build a clean date table:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2030, 12, 31 ) ),
    "Year",        YEAR ( [Date] ),
    "Quarter",     "Q" & QUARTER ( [Date] ),
    "Month",       FORMAT ( [Date], "MMM" ),
    "Month Number", MONTH ( [Date] ),
    "YearMonth",   FORMAT ( [Date], "YYYY-MM" ),
    "FY",          IF ( MONTH ( [Date] ) >= 7,
                       "FY" & ( YEAR ( [Date] ) + 1 ),
                       "FY" & YEAR ( [Date] ) ),
    "Is Weekday",  WEEKDAY ( [Date], 2 ) <= 5,
    "Is Current Month", FORMAT ( [Date], "YYYY-MM" ) = FORMAT ( TODAY (), "YYYY-MM" )
)

Then in the model: right-click Date → "Mark as date table." Without this step, SAMEPERIODLASTYEAR and DATEADD will fail silently on misaligned filters.

Pattern 2: Year-to-Date Sales (Multi-Year Comparable)

This is the bread-and-butter measure. The Odoo subtlety: state must be 'sale' or 'done' — quotations and cancelled orders should not count.

Sales YTD :=
CALCULATE (
    SUMX (
        FILTER (
            'sale_order',
            'sale_order'[state] IN { "sale", "done" }
        ),
        'sale_order'[amount_total]
    ),
    DATESYTD ( 'Date'[Date] )
)

Sales YTD PY :=
CALCULATE ( [Sales YTD], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Sales YTD vs PY % :=
DIVIDE ( [Sales YTD] - [Sales YTD PY], [Sales YTD PY] )

If your fiscal year is non-calendar, replace DATESYTD with DATESYTD('Date'[Date], "06-30") for a July-June fiscal year.

Pattern 3: Multi-Company Consolidation

Odoo lets each res_company have its own currency. Power BI must consolidate to a single reporting currency. Build a currency_rate table (one row per currency-date pair, a join column to Date), then:

Sales (Reporting Currency) :=
SUMX (
    'sale_order',
    'sale_order'[amount_total]
        * RELATED ( 'currency_rate'[rate_to_usd] )
)

Add a Company slicer driven by res_company.name. Now leadership can flip between consolidated USD and local currency per entity.

Pattern 4: Account Hierarchy Flattening

Odoo's account_account.parent_id is self-referential — chart of accounts is a tree. Power BI does not slice well on parent_id directly. Flatten with PATH:

Account Path = PATH ( 'account_account'[id], 'account_account'[parent_id] )
Level 1 = LOOKUPVALUE ( 'account_account'[name], 'account_account'[id], PATHITEM ( [Account Path], 1, INTEGER ) )
Level 2 = LOOKUPVALUE ( 'account_account'[name], 'account_account'[id], PATHITEM ( [Account Path], 2, INTEGER ) )
Level 3 = LOOKUPVALUE ( 'account_account'[name], 'account_account'[id], PATHITEM ( [Account Path], 3, INTEGER ) )
Account Depth = PATHLENGTH ( [Account Path] )

Now use Level 1, Level 2, Level 3 as a hierarchical row in a matrix visual — collapsible P&L done.

Pattern 5: Customer Cohort Retention

Cohort analysis is the highest-value pattern most Odoo teams skip because the SQL is painful. DAX makes it readable.

First Purchase Date :=
CALCULATE (
    MIN ( 'sale_order'[date_order] ),
    ALLEXCEPT ( 'sale_order', 'res_partner'[id] )
)

Cohort Month :=
FORMAT ( [First Purchase Date], "YYYY-MM" )

Months Since Cohort :=
DATEDIFF ( [First Purchase Date], MAX ( 'sale_order'[date_order] ), MONTH )

Active Customers :=
DISTINCTCOUNT ( 'sale_order'[partner_id] )

Cohort Retention % :=
DIVIDE (
    [Active Customers],
    CALCULATE (
        DISTINCTCOUNT ( 'res_partner'[id] ),
        ALLEXCEPT ( 'res_partner', 'sale_order'[Cohort Month] )
    )
)

Pivot rows = Cohort Month, columns = Months Since Cohort, values = Cohort Retention %. Conditional format the matrix and you have a triangle that finance and growth teams will both want.

Pattern 6: Inventory Aging Buckets

Odoo's stock_quant.in_date is when inventory was received. Aging buckets help spot dead stock.

Aging Bucket :=
SWITCH (
    TRUE (),
    DATEDIFF ( 'stock_quant'[in_date], TODAY (), DAY ) <= 30, "0-30 days",
    DATEDIFF ( 'stock_quant'[in_date], TODAY (), DAY ) <= 60, "31-60 days",
    DATEDIFF ( 'stock_quant'[in_date], TODAY (), DAY ) <= 90, "61-90 days",
    DATEDIFF ( 'stock_quant'[in_date], TODAY (), DAY ) <= 180, "91-180 days",
    "180+ days"
)

Inventory Value :=
SUMX (
    'stock_quant',
    'stock_quant'[quantity] * RELATED ( 'product_template'[standard_price] )
)

Stack the inventory value by aging bucket. Anything in the 180+ bucket needs a write-down conversation.

Pattern 7: Day Sales Outstanding (DSO)

DSO is the single most asked-for finance measure. The Odoo logic: only account.move of type out_invoice and out_refund count, posted state, and net of credit notes.

AR Outstanding :=
CALCULATE (
    SUM ( 'account_move_line'[amount_residual] ),
    'account_move_line'[account_internal_type] = "receivable",
    'account_move'[state] = "posted"
)

Trailing 90d Sales :=
CALCULATE (
    [Sales],
    DATESINPERIOD ( 'Date'[Date], TODAY (), -90, DAY )
)

DSO :=
DIVIDE ( [AR Outstanding], [Trailing 90d Sales] / 90 )

A DSO above your payment terms (e.g., 60 on net-30) is a collections issue. Trend it weekly.

Pattern 8: Inventory Turnover

COGS :=
CALCULATE (
    SUM ( 'account_move_line'[debit] ),
    'account_account'[account_type] = "expense_direct_cost"
)

Average Inventory :=
AVERAGEX (
    VALUES ( 'Date'[YearMonth] ),
    [Inventory Value]
)

Inventory Turnover :=
DIVIDE ( [COGS], [Average Inventory] )

Days Inventory Outstanding :=
DIVIDE ( 365, [Inventory Turnover] )

A turnover below 4 in retail or below 8 in fast-moving consumer goods means inventory is parked. Drill by category to find the culprit.

Pattern 9: Composite Key Joins (Multi-Company Lookups)

Odoo schemas use (company_id, code) as a logical key for accounts, products, and warehouses. Power BI does not support multi-column relationships well — workaround:

'account_account'[Composite Key] = 'account_account'[company_id] & "|" & 'account_account'[code]

Add the same calculated column to the fact table you are joining and use single-column relationships on the composite key. This is the cleanest workaround until Power BI ships native composite keys.

Pattern 10: Sales Funnel Conversion

Odoo CRM tracks stages. To compute funnel conversion:

Leads in Stage :=
CALCULATE (
    DISTINCTCOUNT ( 'crm_lead'[id] ),
    USERELATIONSHIP ( 'Date'[Date], 'crm_lead'[date_last_stage_update] )
)

Win Rate :=
DIVIDE (
    CALCULATE ( [Leads in Stage], 'crm_stage'[is_won] = TRUE ),
    [Leads in Stage]
)

Average Deal Size :=
AVERAGEX (
    FILTER ( 'crm_lead', 'crm_stage'[is_won] = TRUE ),
    'crm_lead'[expected_revenue]
)

USERELATIONSHIP is the trick — crm_lead has multiple date columns (created, last update, won) and you want the right one for the visual.

Pattern 11: Manufacturing Yield

For Odoo MRP users:

Planned Quantity :=
SUM ( 'mrp_production'[product_qty] )

Produced Quantity :=
SUM ( 'mrp_production'[qty_produced] )

Yield % :=
DIVIDE ( [Produced Quantity], [Planned Quantity] )

Scrap Rate :=
DIVIDE (
    SUM ( 'stock_move'[product_qty] ),
    [Produced Quantity]
) * IF ( CONTAINSSTRING ( SELECTEDVALUE ( 'stock_picking_type'[code] ), "scrap" ), 1, 0 )

Yield by product, by work center, by week — operations teams will live in this dashboard.

Pattern 12: Calculation Groups for Time Intelligence

This is the modeling shortcut that replaces 30 measures with 5. As of Power BI Desktop 2026-01, calculation groups are stable in the desktop UI.

Create a calculation group called "Time Intelligence" with these calculation items:

-- Current
Current = SELECTEDMEASURE ()

-- YTD
YTD = CALCULATE ( SELECTEDMEASURE (), DATESYTD ( 'Date'[Date] ) )

-- PY
PY = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

-- YoY
YoY = SELECTEDMEASURE () - CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

-- YoY %
[YoY %] =
DIVIDE (
    SELECTEDMEASURE () - CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ),
    CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
)

Now any base measure (Sales, COGS, Margin, Inventory Value) automatically gets YTD, PY, YoY, YoY% variants. Drop the calculation group on a slicer or column header and let users flip between them.

Putting the Patterns Together

A complete Odoo + Power BI dataset uses all 12 patterns:

Build the foundation patterns first. Add domain patterns as stakeholders demand them. Refuse the request to add raw Odoo IDs to visuals — they will lead to confusing filter context bugs.

Performance Notes for Large Odoo Datasets

For Odoo deployments above 10M rows in account_move_line:

1. Switch to Import mode + Incremental Refresh. DirectQuery against Odoo's PostgreSQL backend is too slow once you cross 10M rows. See our incremental refresh deep dive.
2. Pre-aggregate in PostgreSQL. Build materialized views for monthly aggregates. Power BI consumes the views.
3. Star schema, not snowflake. Odoo's normalized schema must be denormalized for VertiPaq to compress efficiently.
4. Disable auto-date hierarchies. Power BI auto-generates a date table per date column — kill this in options or your model bloats.

Frequently Asked Questions

Should I use the Odoo XML-RPC connector or read PostgreSQL directly?

Read PostgreSQL directly via the native Power BI PostgreSQL connector. The XML-RPC connector is fine for small lookups but gets slow over 100K rows. For production dashboards, give Power BI a read-only PostgreSQL user and query the warehouse views.

Do these patterns work for Odoo Online (SaaS)?

Odoo Online does not give you direct PostgreSQL access. You can either use the XML-RPC connector (slow), export to a warehouse via Odoo's automated actions, or use a third-party connector. Most ECOSIRE clients on Odoo Online sync nightly to BigQuery or PostgreSQL via Airbyte and point Power BI at the warehouse.

How do I handle Odoo multi-currency at the line level?

Use currency_id on account_move_line and a currency_rate table. Odoo stores both amount_currency (transaction currency) and balance (company currency). For consolidation across companies, multiply balance by the company-to-reporting currency rate (Pattern 3). Do not use amount_currency for consolidation — you will mix currencies.

Are there ready-made templates?

Yes. ECOSIRE's Power BI dashboard service ships pre-built Odoo dashboards covering sales, inventory, manufacturing, and finance. We license the .pbit templates so your team can extend them. Browse our Power BI products catalog for the current library.

What about Microsoft Fabric? Does it change this?

Fabric absorbs Power BI Premium and adds OneLake, Data Factory, and Synapse. The DAX patterns above are unchanged — they live in the Power BI semantic model regardless of capacity SKU. Fabric mainly changes how data lands and is governed before it reaches the model.

---

These twelve patterns cover the 80% of Odoo + Power BI dashboard requests that come across our desk. If you need help implementing them on your specific Odoo version, or you want a managed Power BI deployment with refresh schedules, RLS, and embedded portals, our Power BI consultancy team builds these into production every month.