Your first DAX measures in Power BI: SUM, CALCULATE, and YTD vs last year

Once your Power BI model is shaped as a star schema with a marked date table, the next job is writing measures: the formulas behind your totals, comparisons, and percentages. This guide is for the SMB analyst who can drag fields onto a chart but has never written DAX (Data Analysis Expressions, the formula language behind Power BI). By the end you will have a SUM measure, a filtered CALCULATE measure, two time-intelligence measures, a year-over-year percentage, and a method for debugging numbers that come out wrong.

Before you start

• A Power BI Desktop file with a star schema: at least one fact table and a dedicated date table that you have already marked as a date table.
• One numeric column in your fact table to add up, for example Sales[Amount].
• The Fields pane visible on the right.

1. Know the difference: measure versus calculated column

This is the single most useful distinction in DAX.

• A calculated column is computed once, row by row, and stored in the table. Use it for an attribute you want to slice or group by (for example, a Margin Band of Low/Medium/High).
• A measure is computed on the fly, based on whatever filters are active in the visual. Use it for anything you add up or compare (totals, averages, ratios).

The common mistake is building a calculated column to sum a value, then dragging it onto a chart and getting a wrong, frozen number. If you are aggregating, you almost always want a measure.

2. Write a SUM measure

1. In the Fields pane, right-click your fact table and choose New measure.
2. In the formula bar, enter:

Total Sales = SUM ( Sales[Amount] )

3. Press Enter. Format it on the Measure tools ribbon (set it to Currency).
4. Drag Total Sales onto a card visual to confirm it shows your grand total.

3. Filter a measure with CALCULATE

CALCULATE is the workhorse of DAX. It evaluates an expression under a filter you specify.

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

This reuses your Total Sales measure but applies an additional filter for the Online channel. Put Total Sales and Online Sales side by side in a table and confirm the online figure is smaller than the total.

4. Add year-to-date with TOTALYTD

With a marked date table in place, time intelligence works.

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

Drop Sales YTD into a table broken down by month and confirm each month's value accumulates from the start of the year.

5. Compare to last year with SAMEPERIODLASTYEAR

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

This returns the same period one year earlier. In a table by month, the value in a given month should equal Total Sales from the same month last year.

6. Build a year-over-year percentage

Now combine the two into a growth percentage, guarding against dividing by blank:

Sales YoY % =
DIVIDE (
    [Total Sales] - [Sales LY],
    [Sales LY]
)

Use DIVIDE, not the slash operator, because DIVIDE returns blank instead of an error when last year's value is missing. Format the result as a percentage on the Measure tools ribbon.

7. Debug a wrong number

When a measure is wrong, work through these in order.

1. Check the filter context. Click the visual and read what slicers and rows are filtering it. A number is only wrong relative to what you expected it to be filtered by.
2. Strip it down. Put the base measure (Total Sales) next to the suspect measure in a simple table. If the base is right and the derived one is wrong, the problem is in the added logic.
3. Check the date table. Confirm it is marked and continuous. Most time-intelligence bugs trace back here.
4. Check relationships. A measure that ignores a slicer usually means the slicer's table is not related to the fact table, or the cross-filter direction is wrong.
5. Watch for bidirectional filters. If a total looks inflated or blank after a model change, look for a relationship someone set to Both.

8. Verify your measures

Build one table visual with Month on rows and four columns: Total Sales, Sales LY, Sales YTD, and Sales YoY %. Check that Sales LY in the current year matches Total Sales from the prior year, and that the percentage moves the right direction. Cross-check one cell against a manual calculation so you trust the rest.

What to do next

These five patterns (SUM, filtered CALCULATE, year-to-date, same-period-last-year, and a guarded percentage) cover a large share of everyday SMB reporting. As your needs grow, you will reach for more time intelligence and reusable measure tables. If your measures are coming out wrong and you cannot tell whether it is the formula or the model underneath, that is a common place to get stuck, and we are happy to sit down and untangle it with you.