Can Copilot Really Write Your DAX? 10 Measures from a Real Sales Model

The Scenario: A Sales Report Under Pressure

You’re maintaining a Power BI dataset for a regional sales team:

• Fact table Sales (Import mode)
  • Sales[OrderDate]
  • Sales[InvoiceDate]
  • Sales[Quantity]
  • Sales[NetAmount]
  • Sales[CostAmount]
  • Sales[Channel] (values like Online, Store)
• Dimension Date with a proper date table marked as such
• Dimension Customer with Customer[CustomerID], Customer[Segment]

The team wants a set of new measures. Instead of writing them manually, you use Copilot in Power BI Desktop to generate DAX from natural language prompts.

Below, I’ll show:

• The kind of prompt practitioners actually type
• A plausible Copilot-style measure
• What’s technically right or wrong
• A corrected version where needed

I’m not reproducing Copilot verbatim (that will vary), but each "Copilot" measure reflects patterns Copilot currently tends to produce: heavy use of CALCULATE, reliance on implicit filters, and limited awareness of modelling intent.

Measure 1 – Total Sales Amount

Prompt: Create a measure for total net sales amount

Copilot-style output:

Total Sales Amount = SUM ( Sales[NetAmount] )

Verdict: Good.

For a numeric column in a single fact table, Copilot usually gets this right. No need for CALCULATE or SUMX here.

Keep as-is.

Measure 2 – Gross Margin

Prompt: Create a measure for gross margin in currency

Copilot-style output:

Gross Margin = SUM ( Sales[NetAmount] ) - SUM ( Sales[CostAmount] )

Verdict: Acceptable, but we can tighten it.

This works as long as both columns are in the same table and numeric. The measure is additive and respects filter context.

Some teams prefer wrapping base measures in VARs for readability, but functionally this is fine.

Optional refinement:

Gross Margin = 
    [Total Sales Amount] - SUM ( Sales[CostAmount] )

This keeps logic aligned with your main revenue measure.

Measure 3 – Gross Margin %

Prompt: Create a measure for gross margin percentage of sales

Copilot-style output:

Gross Margin % = 
    DIVIDE ( 
        SUM ( Sales[NetAmount] ) - SUM ( Sales[CostAmount] ),
        SUM ( Sales[NetAmount] )
    )

Verdict: Technically correct.

The use of DIVIDE instead of / is good: it handles divide-by-zero safely and returns BLANK by default. This measure respects filter context and works for visuals by date, customer, channel, etc.

Recommended refinement:

Gross Margin % = 
    DIVIDE ( 
        [Gross Margin],
        [Total Sales Amount]
    )

Using base measures keeps definitions consistent and easier to audit.

Measure 4 – Year-to-Date Sales

Prompt: Create a measure for year-to-date total sales using the Date table

Copilot-style output:

Sales YTD = 
    TOTALYTD ( 
        [Total Sales Amount],
        'Date'[Date]
    )

Verdict: Correct, assuming:

• 'Date' is marked as a date table
• 'Date'[Date] is the key date column
• You want calendar year behavior

TOTALYTD uses the current filter context and the date table to accumulate values from the start of the year up to the max visible date.

Things Copilot won’t check for you:

• If your model mixes OrderDate and InvoiceDate, Copilot doesn’t know which business logic you want. The measure above uses whatever Total Sales Amount uses.

If you need YTD by invoice date specifically, you must encode that logic yourself, e.g. using a separate date table or mapping.

Measure 5 – Sales Last Year (Same Period)

Prompt: Create a measure for sales in the same period last year

Copilot-style output:

Sales LY = 
    CALCULATE ( 
        [Total Sales Amount],
        SAMEPERIODLASTYEAR ( 'Date'[Date] )
    )

Verdict: Correct for standard calendar comparisons.

SAMEPERIODLASTYEAR shifts the current filter on 'Date'[Date] by one year while preserving the shape (e.g. entire year, month-to-date, day-level).

Watch out:

• It relies on a continuous date range with no gaps in the date table.
• It does not handle custom fiscal calendars automatically. For fiscal calendars, you’d typically use DATEADD on a fiscal-date column or custom logic.

Fiscal example (if you have a fiscal date column):

Sales LY Fiscal = 
    CALCULATE ( 
        [Total Sales Amount],
        DATEADD ( 'Date'[FiscalDate], -1, YEAR )
    )

Copilot won’t infer your fiscal calendar; you must guide it with explicit column names and intent.

Measure 6 – Rolling 12-Month Sales

Prompt: Create a measure for sales over the last 12 months from the selected date

Typical Copilot-style output:

Sales Rolling 12M = 
    CALCULATE ( 
        [Total Sales Amount],
        DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH )
    )

Verdict: Correct pattern for rolling windows.

DATESINPERIOD builds a date range from the max visible date, going back 12 months. CALCULATE then evaluates [Total Sales Amount] over that range.

Two things to verify manually:

• Your 'Date'[Date] is indeed the active date used for sales.
• You understand that this is last 12 months (not current year to date).

Copilot often picks MAX('Date'[Date]) automatically. In most visuals this is fine, but if you’re mixing multiple date tables or using disconnected date slicers, you need to be explicit.

Measure 7 – Distinct Customers This Period

Prompt: Create a measure for distinct customers in the current filter context

Copilot-style output:

Distinct Customers = DISTINCTCOUNT ( Customer[CustomerID] )

Verdict: Correct, assuming:

• Customer[CustomerID] is the unique key
• Relationships between Sales and Customer are correctly set (single direction or bi-directional as intended)

This measure counts customers filtered by any slicers or visual context (date, segment, etc.).

If your model uses surrogate keys or composite keys, Copilot won’t know; you must point it to the right column.

Measure 8 – Average Order Value

Prompt: Create a measure for average order value (sales per order)

Common Copilot-style output:

Average Order Value = 
    DIVIDE ( 
        SUM ( Sales[NetAmount] ),
        COUNTROWS ( Sales )
    )

Verdict: Often wrong for real models.

This assumes each row in Sales is a single order. In many models, each row is a line item, and COUNTROWS(Sales) counts lines, not orders.

A more robust definition uses an order ID column and DISTINCTCOUNT:

Average Order Value = 
    DIVIDE ( 
        [Total Sales Amount],
        DISTINCTCOUNT ( Sales[OrderID] )
    )

Why Copilot struggles here:

• It cannot infer your grain (order vs line vs invoice) from the schema alone.
• It tends to reach for COUNTROWS because it’s generic.

When asking Copilot for measures that depend on table grain, be explicit in the prompt: mention line items vs orders and the exact key column.

Measure 9 – Online Channel Share of Sales

Prompt: Create a measure for the percentage of sales from the Online channel

Typical Copilot-style output:

Online Sales % = 
    DIVIDE ( 
        CALCULATE ( [Total Sales Amount], Sales[Channel] = "Online" ),
        [Total Sales Amount]
    )

Verdict: Correct pattern, but there are two subtle points.

1. Boolean filter expression – The filter Sales[Channel] = "Online" inside CALCULATE is evaluated as a table filter over Sales. This is valid DAX and works as intended.

2. Row-level security (RLS) and filter propagation – If you have RLS or complex relationships, the filter inside CALCULATE is applied after existing filters. That’s usually what you want for "share of" measures.

The measure returns the share of online sales relative to the total in the current filter context.

If you want the share within each segment or region:

The same measure works; the outer filter context (segment, region, date) applies to both numerator and denominator.

This is one of the cases where Copilot’s default is generally safe.

Measure 10 – Sales vs Target (Variance and Variance %)

Your model now includes a Target table:

• Target[Date]
• Target[Segment]
• Target[TargetAmount]

It’s related to Date and Customer/Segment appropriately.

Prompt: Create measures for sales variance vs target and variance percentage

Common Copilot-style output for variance:

Sales vs Target = 
    [Total Sales Amount] - SUM ( Target[TargetAmount] )

Verdict: This can be technically correct if relationships are set, but it’s fragile:

• SUM(Target[TargetAmount]) relies entirely on relationships and filter context to align target with the same date/segment filters as sales.
• Copilot doesn’t verify whether Target is correctly related or if there are multiple targets per combination.

A more explicit pattern uses a base measure for target:

Target Amount = SUM ( Target[TargetAmount] )

Sales Variance = [Total Sales Amount] - [Target Amount]

Sales Variance % = 
    DIVIDE ( [Sales Variance], [Target Amount] )

This is still filter-context driven, but separating the base target measure makes it easier to test and debug.

Where Copilot often fails:

• If you ask for "variance vs last year and vs target" in one go, it may mix time-intelligence functions (SAMEPERIODLASTYEAR) with target logic incorrectly.
• It may try to use CALCULATE with filters on the Target table that don’t match your relationship design.

For anything that combines:

• Time intelligence
• Secondary tables (budget/target)
• Custom business logic

Copilot can suggest starting points, but you must control the filter context explicitly.

How Copilot Behaved Across the 10 Measures

Across these measures, a pattern emerges:

What Copilot did well

• Simple aggregations (SUM, DISTINCTCOUNT) on obvious numeric and key columns
• Basic ratios using DIVIDE
• Standard time-intelligence with a marked date table (TOTALYTD, SAMEPERIODLASTYEAR, DATESINPERIOD)
• Simple share-of calculations with CALCULATE and a single filter condition

Where Copilot struggled or became risky

• Measures that depend on table grain (orders vs lines)
• Business-specific logic (invoice date vs order date, fiscal calendars)
• Multi-table logic with targets/budgets where relationships matter
• Combined requirements in a single prompt (e.g. "rolling, fiscal, vs target" in one sentence)

Copilot currently has no understanding of your business semantics beyond what’s implicitly visible in column names and relationships. It doesn’t validate the grain or cardinality of tables and cannot see whether a measure aligns with how the business actually thinks about the metric.

Practical Way to Use Copilot for DAX in Real Projects

For a team under pressure to deliver, here’s a pattern that works:

1. Use Copilot for scaffolding, not final DAX

   • Let it generate first drafts of simple measures.
   • Immediately review the filter context and table grain assumptions.

2. Be explicit in prompts

   • Mention the grain: "Each row in Sales is a line item; OrderID is the order key."
   • Mention the date logic: "Use InvoiceDate, not OrderDate."
   • Mention fiscal vs calendar behavior.

3. Standardize on base measures

   • Define core measures manually: total sales, total cost, total target.
   • Ask Copilot to build derived measures on top of these.

4. Test measures in focused visuals

   • Simple table with Date and the new measure.
   • Another with Customer/Segment and the measure.
   • Check that totals and subtotals match expectations.

5. Avoid Copilot for complex time-intelligence + target logic

   • Let Copilot suggest fragments, but assemble the final measure yourself.

Copilot is fast at writing syntactically valid DAX. It’s not yet good at encoding the implicit rules your team uses to interpret metrics.

One Takeaway for Your Next Sprint

Treat Copilot as a junior analyst who can type DAX quickly but doesn’t understand your business model. Let it generate the first draft of simple measures, but always review:

• What table grain it assumes
• Which date and dimension columns it uses for filter context

If those two checks become standard in your team’s review process, Copilot can genuinely save time without quietly corrupting your numbers.