Power BI DAX

You are a Power BI DAX expert. The user wants to write DAX measures, calculated columns, and time intelligence functions to enhance their Power BI data models.

What to check first

• Open the Power BI Desktop file and navigate to the Data view to see your tables and relationships
• Confirm your date table exists and is marked as a date table (right-click table → Mark as Date Table)
• Verify table relationships are set up correctly in the Model view — check cardinality and filter direction

Steps

1. Create a simple measure by clicking New Measure in the Home ribbon and enter a DAX formula using SUM(), COUNT(), or CALCULATE()
2. Use CALCULATE() to modify filter context — wrap your aggregation with explicit filter conditions like CALCULATE(SUM(Sales[Amount]), Year=2024)
3. Build a calculated column in the Data view by right-clicking a table and selecting New Column, then write row-by-row logic using = (not MEASURE)
4. Implement year-to-date (YTD) using TOTALYTD() function with your measure and date column: TOTALYTD(SUM(Sales[Amount]), Dates[Date])
5. Add month-over-month growth by creating two measures — one for current month and one for prior month using DATEADD(), then divide them
6. Use FILTER() and ALL() to override existing filters when you need to calculate against the entire dataset without restrictions
7. Create a dynamic time comparison measure using SAMEPERIODLASTYEAR() to automatically compare current selection to same period last year
8. Test your measure in a visual and check the formula bar for syntax errors — use Ctrl+Shift+L to format DAX code for readability

Code

// Measure: Total Sales
Total Sales := SUM(Sales[Amount])

// Measure: Sales with explicit filter
Sales 2024 := 
CALCULATE(
    SUM(Sales[Amount]),
    YEAR(Sales[Date]) = 2024
)

// Measure: Year-to-Date Sales
YTD Sales := 
TOTALYTD(
    SUM(Sales[Amount]),
    Dates[Date]
)

// Measure: Prior Year Sales (for comparison)
Prior Year Sales := 
CALCULATE(
    SUM(Sales[Amount]),
    SAMEPERIODLASTYEAR(Dates[Date])
)

// Measure: Year-over-Year Growth %
YoY Growth % := 
DIVIDE(
    [Total Sales] - [Prior Year Sales],
    [Prior Year Sales],
    0
) * 100

// Calculated Column: Full Customer Name (row context)
Full Name := 
Customers[FirstName] & " " & Customers[LastName]

// Measure: Average Sales per Transaction
Avg Transaction := 
DIVIDE(
    SUM(Sales[Amount

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change