Star Schema Sales Model with DAX

Transform a flat dataset into a star schema and write interview-level DAX measures.

Introduction

30 Second Summary

Every company has sales data. The difference between a junior and senior analyst is knowing how to model that data so the right answers are always one click away.

In this project, you will take a flat sales dataset in Power BI and transform it into a clean star schema data model. Then you will write the exact DAX measures that hiring managers test for in analyst interviews and build an interactive report.

What You'll Build

A normalized star schema data model with DAX measures covering totals, ratios, year-over-year growth, and time intelligence, all visualized in an interactive sales report built from Microsoft's built-in Financial Sample dataset.

By the end of this project, you'll have:

  • A star schema with 5 tables (1 fact + 4 dimensions) built from a single flat dataset using Power Query.
  • A dedicated Date table created with DAX and 12 DAX measures covering interview-tested patterns like CALCULATE, TOTALYTD, year-over-year growth, and profit margins.
  • A polished, interactive sales report with KPI cards, trend comparison charts, and a time intelligence matrix with conditional formatting.
  • Secret Mission: Add a dynamic Top N product filter using a disconnected slicer table and RANKX.

Are there any prerequisites?

You should have basic familiarity with Power BI Desktop (loading data and creating visuals) and a basic understanding of tables and relationships. Power BI Desktop is free and runs on Windows.

Before We Start

Before We Start...

Load the Financial Sample Data

Every clean data model starts with understanding the raw data. In this project, you will work with Microsoft's Financial Sample dataset, a single flat table of ~700 rows covering sales transactions across countries, products, and market segments.

Before you can build a star schema, you need to see what the "before" picture looks like. In this step, you will load the dataset and inspect its structure.

In this step, get ready to:
  • Load the built-in Financial Sample dataset into Power BI Desktop.
  • Inspect the table columns and structure in Data view.
  • Preview the Model view to see the starting state.
Load the Financial Sample dataset

Power BI Desktop ships with a built-in Financial Sample dataset. You will load it directly from the Home screen instead of downloading an external file.

  • Press the Windows key to open your search bar.
  • Type Power BI Desktop and press Enter to open it.
  • On the Home screen, click Use sample data.
  • In the dialog that appears, click Load sample data.
  • In the Navigator window, select the checkbox next to Financials.
  • Click Load.

Power BI loads the dataset. This may take a few seconds.

What is the Financial Sample dataset?

The Financial Sample is a built-in dataset that ships with Power BI Desktop. It contains ~700 rows of fictional sales data covering segments, countries, products, dates, and financial metrics like sales, costs, and profit.

Everything lives in a single flat table. That makes it perfect for a star schema normalization exercise, because your job is to split it apart.

Don't see the Use sample data option?

Make sure you are on the Power BI Desktop Home screen, not inside a report. Close any open reports to return to Home.

If you still do not see it, your version of Power BI Desktop may be outdated. Update to the latest version from the Microsoft Store.

Help me find the Use sample data option in Power BI Desktop.

Inspect the data in Data view

Now that the data is loaded, take a close look at what you are working with.

  • Click the Data view icon in the left sidebar. It looks like a table grid.

You should see a single table called financials with 16 columns.

The columns include Segment, Country, Product, Discount Band, Units Sold, Manufacturing Price, Sale Price, Gross Sales, Discounts, Sales, COGS, Profit, Date, Month Number, Month Name, and Year.

What is a denormalized table?

Notice how product details, segment info, country info, and sales metrics are all packed into one table. This is called a denormalized dataset.

Product names repeat on every row that product appears in. Country names repeat on every row for that country. This redundancy makes the data harder to manage and model at scale.

Preview the Model view
  • Click the Model view icon in the left sidebar. It looks like three connected boxes.

Right now you see a single table with no relationships. By the end of this project, you will have 5 tables connected in a star pattern. This is your "before" picture.

What is a star schema?

A star schema organizes data into a central fact table (containing numeric measurements like sales and profit) surrounded by dimension tables (containing descriptive attributes like product names and countries).

When you look at the finished model from above, the dimension tables radiate outward from the fact table like points on a star. This is the design pattern Microsoft recommends for Power BI, and the one hiring managers expect to see.

Your raw data is loaded and inspected. Next up, you will split this flat table into a proper star schema using Power Query.

Build the Star Schema with Power Query

You've loaded the Financial Sample dataset and seen how all the data lives in a single flat table. Now it's time to split that table into a proper star schema.

You'll use Power Query Editor to create separate dimension and fact tables from the flat dataset. A clean star schema makes DAX calculations easier to write and faster to run.

In this step, get ready to:
  • Duplicate and rename queries in Power Query Editor.
  • Build three dimension tables: DimProduct, DimSegment, and DimCountry.
  • Build the FactSales table by merging in dimension keys and removing redundant columns.
Set up your queries in Power Query Editor

Before you can build individual tables, you need a separate query for each one. You'll duplicate the original financials query four times to create the raw material for your dimension and fact tables.

  • Click Transform data on the Home tab to open the Power Query Editor.
  • In the Queries pane on the left, right-click the financials query.
  • Select Duplicate.
  • Repeat the right-click and Duplicate three more times so you have four copies total.
  • Double-click each duplicated query name in the Queries pane to rename them. Rename them to: DimProduct, DimSegment, DimCountry, and FactSales.
  • Double-click the original financials query name and rename it to _OriginalData.
  • Right-click _OriginalData and uncheck Enable load.

You should now see five queries in the Queries pane. The _OriginalData query should appear italicized, which means its load is disabled.

Why disable load on the original data?

Disabling load keeps the raw flat table as a reference inside Power Query without loading it into your data model. This avoids duplicate data in the model while still letting you trace back to the original dataset if needed.

Don't see five queries?

Make sure you right-clicked directly on the financials query name in the Queries pane each time. If you duplicated the wrong query, delete the extras (right-click and select Delete) and duplicate the original again.

Help me fix my Power Query queries.

Build the dimension tables

Dimension tables hold descriptive attributes that you filter and group by. Each dimension gets its own key column that the fact table will reference later.

What is a star schema?

A star schema organizes data into a central fact table (containing numbers you measure, like sales and profit) surrounded by dimension tables (containing descriptive attributes you filter by, like product name and country).

This is the model structure Microsoft recommends for Power BI. It reduces data redundancy and lets DAX measures run against clean, unambiguous relationships.

Start with DimProduct.

  • Click the DimProduct query in the Queries pane.
  • Hold Ctrl (Windows) or Cmd (macOS) and click the Product, Manufacturing Price, and Sale Price column headers to select all three.
  • Right-click any of the selected column headers and choose Remove Other Columns.
  • Click Remove Rows on the Home tab, then select Remove Duplicates.
  • Go to the Add Column tab and click Index Column.
  • Select From 1 from the dropdown.
  • Double-click the new Index column header and rename it to ProductKey.

Your DimProduct preview should show four columns: Product, Manufacturing Price, Sale Price, and ProductKey. Each row represents a unique product.

Now build DimSegment using the same pattern.

  • Click the DimSegment query in the Queries pane.
  • Click the Segment column header to select it.
  • Right-click the column header and choose Remove Other Columns.
  • Click Remove Rows on the Home tab, then select Remove Duplicates.
  • Go to Add Column and click Index Column, then select From 1.
  • Double-click the new Index column header and rename it to SegmentKey.

DimSegment should now show two columns: Segment and SegmentKey.

Finally, build DimCountry.

  • Click the DimCountry query in the Queries pane.
  • Click the Country column header to select it.
  • Right-click the column header and choose Remove Other Columns.
  • Click Remove Rows on the Home tab, then select Remove Duplicates.
  • Go to Add Column and click Index Column, then select From 1.
  • Double-click the new Index column header and rename it to CountryKey.

DimCountry should now show two columns: Country and CountryKey.

Seeing more rows than expected?

If a dimension table has more rows than unique values, you may have forgotten to remove duplicates. Click the query with extra rows, then go to Home and select Remove Rows then Remove Duplicates.

Help me troubleshoot my dimension tables in Power Query.

Build the FactSales table

The fact table stores the numbers you measure, like sales, profit, and units sold. Instead of keeping the original text columns (like product names and country names), the fact table references each dimension through a foreign key.

You'll use Power Query's Merge Queries feature to look up each dimension key and bring it into FactSales. Then you'll remove the redundant text columns.

  • Click the FactSales query in the Queries pane.
  • Click Merge Queries on the Home tab.
  • In the top table preview, click the Product column header to select it as the matching column.
  • In the second table dropdown, select DimProduct.
  • Click the Product column in the DimProduct preview to set the match.
  • Leave the join kind as Left Outer and click OK.
  • Find the new DimProduct column in the preview (far right). Click the expand icon (double-arrow) on the column header.
  • Uncheck all columns except ProductKey.
  • Uncheck Use original column name as prefix, then click OK.

You should now see a ProductKey column at the far right of the FactSales preview.

What does Merge Queries do?

Merge Queries works like a SQL JOIN. It matches rows between two tables on a shared column and lets you pull in columns from the second table. Here, you're matching on Product to look up the corresponding ProductKey from DimProduct.

Now repeat the merge process for DimSegment.

  • Click Merge Queries on the Home tab again.
  • Click the Segment column in the top table preview.
  • Select DimSegment as the second table and click its Segment column to match.
  • Leave the join kind as Left Outer and click OK.
  • Expand the new DimSegment column. Keep only SegmentKey, uncheck the prefix option, and click OK.

Repeat once more for DimCountry.

  • Click Merge Queries on the Home tab.
  • Click the Country column in the top table preview.
  • Select DimCountry as the second table and click its Country column to match.
  • Leave the join kind as Left Outer and click OK.
  • Expand the new DimCountry column. Keep only CountryKey, uncheck the prefix option, and click OK.

Your FactSales preview should now have three new key columns at the far right: ProductKey, SegmentKey, and CountryKey.

Now remove the original text columns that the dimension keys replace.

  • Hold Ctrl (Windows) or Cmd (macOS) and click each of these column headers to select them: Product, Segment, Country, Discount Band, Manufacturing Price, Sale Price, Month Number, Month Name, and Year.
  • Right-click any of the selected column headers and choose Remove Columns.

Why remove these columns?

The fact table should only contain foreign keys (to link to dimensions), the date column, and numeric measures. Keeping the original text columns would duplicate data that already lives in the dimension tables.

Your FactSales preview should now show exactly these columns: Units Sold, Gross Sales, Discounts, Sales, COGS, Profit, Date, ProductKey, SegmentKey, and CountryKey.

  • Click Close & Apply on the Home tab to load your star schema into the model.

Power BI will process the queries and return you to the main report view.

  • Switch to Model view by clicking the three-connected-boxes icon on the left sidebar.

You should see four tables in the diagram: DimProduct, DimSegment, DimCountry, and FactSales. They are not connected yet. You'll wire up the relationships in the next step.

Seeing an error after Close & Apply?

A common cause is a mismatched merge. If the merge columns don't align (e.g., you matched Segment to the wrong table), the key column will be blank. Go back to Home and click Transform data to reopen Power Query. Check each merge step in the Applied Steps pane on the right side of the FactSales query.

If you see the wrong number of tables in Model view, make sure _OriginalData has load disabled. Right-click it in Power Query and confirm Enable load is unchecked.

Help me troubleshoot my Power Query star schema.

Your star schema tables are built and loaded into the model. Next, you'll create a dedicated Date table and wire up the relationships between all your tables.

Create a Date Table and Set Up Relationships

Your dimension and fact tables are loaded into the model. The raw pieces of the star schema are in place.

The missing piece is a dedicated date table. Time intelligence functions in DAX won't work without one, so you'll create it with a DAX formula and then wire up all the relationships.

In this step, get ready to:
  • Create a DimDate table using a DAX formula.
  • Set up one-to-many relationships between all dimension tables and the fact table.
  • Hide technical key columns from the report view.
Create the DimDate table with DAX

Power BI's time intelligence functions calculate things like year-to-date totals and year-over-year comparisons. They all require a continuous date table that covers every single day in your data range.

  • Switch to Report view by clicking the bar chart icon on the left sidebar.
  • Click the Modeling tab on the ribbon.
  • Click New Table.
  • In the formula bar that appears at the top, replace the default text with this DAX formula:
DimDate =
ADDCOLUMNS(
    // Generate a continuous calendar from earliest to latest date in the model
    CALENDARAUTO(),
    // Add descriptive columns for each date component
    "Year", YEAR([Date]),
    "Month Number", MONTH([Date]),
    "Month Name", FORMAT([Date], "MMMM"),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "Day", DAY([Date])
)

What does this formula do?

CALENDARAUTO() scans every Date column in your model and generates a table with one row per day. It spans from January 1 of the earliest year to December 31 of the latest year.

ADDCOLUMNS wraps that date table and adds computed columns for Year, Month Number, Month Name, Quarter, and Day. These columns give you the fields you need for slicing and grouping data by time periods in your reports.

  • Press Enter or click the checkmark icon in the formula bar to confirm the formula.
  • Switch to Data view by clicking the table icon on the left sidebar.
  • Select the DimDate table in the Data pane on the right.

You should see a table with six columns: Date, Year, Month Number, Month Name, Quarter, and Day. Each row represents a single day.

Don't see the DimDate table?

  • Check the formula bar for any red underlines or error messages. A missing comma or misspelled function name is the most common cause.
  • Make sure you clicked New Table from the Modeling tab, not New Measure. Tables and measures use different formula contexts.
  • Help me debug my DimDate DAX formula in Power BI.

The DimDate table exists, but Power BI doesn't know it's meant to be used for time intelligence yet. You need to mark it explicitly.

  • Select the DimDate table in the Data pane on the right.
  • Click the Table tools tab that appears on the ribbon.
  • Click Mark as date table.
  • In the dropdown, click Mark as date table again.
  • In the dialog that appears, select Date from the Date column dropdown.
  • Click OK.

Why mark it as a date table?

Time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR require Power BI to know which table is the official date table. Marking it also validates that the Date column contains no gaps, no duplicates, and spans full calendar years.

Without this step, your time intelligence measures will return errors. This is a requirement that interviewers specifically test for.

Set up one-to-many relationships

With all five tables loaded, it's time to connect them. Each dimension table needs a one-to-many relationship to the central fact table.

  • Switch to Model view by clicking the diagram icon (three connected boxes) on the left sidebar.

You should see all five tables laid out on the canvas. They aren't connected yet.

  • Drag ProductKey from the DimProduct table and drop it on ProductKey in the FactSales table.
  • Drag SegmentKey from the DimSegment table and drop it on SegmentKey in the FactSales table.
  • Drag CountryKey from the DimCountry table and drop it on CountryKey in the FactSales table.
  • Drag Date from the DimDate table and drop it on Date in the FactSales table.

Four lines now connect the dimension tables to FactSales. Your model should look like a star, with FactSales at the center and the four dimension tables radiating outward.

  • Double-click any relationship line to open the Edit relationship dialog.
  • Confirm the Cardinality is set to One to many.
  • Confirm the Cross filter direction is set to Single.
  • Click Cancel to close the dialog without changes.
  • Repeat this check for each of the other three relationship lines.

What is one-to-many with single cross-filter?

One-to-many means each row in the dimension table (the "one" side) can relate to many rows in the fact table. For example, one product can appear in hundreds of sales transactions.

Single cross-filter direction means filters flow from the dimension to the fact table, not the other way around. This is the standard pattern for a star schema and the default that Power BI creates automatically.

Seeing a many-to-many relationship?

  • This usually means one of your dimension tables has duplicate values in its key column. Go back to Power Query (Home > Transform data) and confirm you applied Remove Duplicates on each dimension table.
  • Also check that you added the Index column after removing duplicates, not before. Adding an index first makes every row unique and prevents Remove Duplicates from actually deduplicating.
  • Help me fix my many-to-many relationship in Power BI.
Hide technical columns from report view

Key columns like ProductKey and SegmentKey exist only to connect tables. Report authors never need to drag them into visuals, so hiding them keeps the field list clean and prevents mistakes.

  • In Model view, right-click the ProductKey column in the DimProduct table.
  • Select Hide in report view.
  • Right-click SegmentKey in DimSegment and select Hide in report view.
  • Right-click CountryKey in DimCountry and select Hide in report view.
  • Now right-click ProductKey in the FactSales table and select Hide in report view.
  • Repeat for SegmentKey, CountryKey, and Date in FactSales.

Why hide the Date column in FactSales?

Report authors should filter dates through the DimDate table, not through FactSales[Date]. Hiding it ensures all date-based slicing goes through your dedicated date table, which is the one marked for time intelligence.

  • Switch to Report view by clicking the bar chart icon on the left sidebar.
  • Expand the FactSales table in the Data pane on the right.

You should see only the numeric measure columns: Units Sold, Gross Sales, Discounts, Sales, COGS, and Profit. The key columns and Date column are no longer visible.

Still seeing key columns in the Data pane?

  • Hidden columns only disappear in Report view. In Data view and Model view, they remain visible but appear slightly faded. Make sure you're checking from Report view.
  • If a column still shows in Report view, right-click it in Model view and confirm Hide in report view has a checkmark next to it.
  • Help me figure out why hidden columns still appear in my Power BI report.

Your star schema is complete with five tables, four relationships, and a clean field list. Next up, you'll write the DAX measures that bring this model to life.

Write Core DAX Measures

Your star schema is built and all the relationships are in place. Now it is time to make that model actually useful.

DAX measures are the formulas that turn raw columns into business insights. In this step, you will write seven measures covering the exact patterns hiring managers test in Power BI interviews.

In this step, get ready to:
  • Write base aggregation measures for sales, profit, units, and margin.
  • Create CALCULATE-based measures that override filter context.
  • Build card and matrix visuals to verify your measures work correctly.
Create base aggregation measures

Every report needs a foundation of core calculations. You will start with four measures that cover the basics: totals and a profit margin ratio.

  • Select the FactSales table in the Fields pane on the right side of the screen.
  • Click New Measure on the Home tab in the ribbon.
  • Enter this formula in the formula bar and press Enter:
Total Sales = SUM(FactSales[Sales])
  • Create your Total Profit measure by clicking New Measure again and entering this formula:
Total Profit = SUM(FactSales[Profit])
  • Create a Total Units measure by clicking New Measure and entering:
Total Units = SUM(FactSales[Units Sold])
  • Create one more measure for Profit Margin % by clicking New Measure and entering:
Profit Margin % = DIVIDE([Total Profit], [Total Sales], 0)

What do these measures do?

Total Sales, Total Profit, and Total Units each use SUM to add up every value in their column. When you drop one of these measures into a visual, Power BI automatically slices the total by whatever dimensions are on the visual.

Profit Margin % uses DIVIDE instead of the / operator. DIVIDE handles division by zero gracefully by returning the third argument (0) instead of an error. This is a DAX best practice that interviewers specifically look for.

  • In the Fields pane, expand the FactSales table.

You should see all four measures listed with a calculator icon next to each name.

Don't see the calculator icons?

  • Make sure you selected the FactSales table before clicking New Measure. If a measure landed in the wrong table, right-click it in the Fields pane, select Home table, and change it to FactSales.
  • Check for typos in the formula bar. Column names like FactSales[Units Sold] must match exactly, including the space.
  • If you see a yellow warning bar, read the error message. It usually points to a misspelled column or table name.

Why aren't my DAX measures showing up in the FactSales table?

Write CALCULATE measures for filter manipulation

CALCULATE is the single most important function in DAX. It evaluates a measure under a modified filter context, letting you override the filters that slicers and visuals normally apply.

You will use CALCULATE with ALL to calculate each country's share of total sales. This pattern is one of the most common interview questions.

  • Make sure FactSales is still selected in the Fields pane.
  • Create a Total Sales All Countries measure by clicking New Measure and entering this formula:
Total Sales All Countries =
CALCULATE(
    [Total Sales],
    ALL(DimCountry)
)
  • Create a Country Sales % measure by clicking New Measure and entering:
Country Sales % =
DIVIDE(
    [Total Sales],
    [Total Sales All Countries],
    0
)

What does the CALCULATE + ALL pattern do?

Total Sales All Countries uses CALCULATE with ALL(DimCountry) to remove any country filter. Even if a slicer or visual row is filtering to one country, this measure ignores that filter and returns the grand total across all countries.

Country Sales % then divides the current-context sales (filtered to one country) by that grand total. The result is each country's percentage share of total sales.

  • Expand FactSales in the Fields pane and confirm you see six measures total (the four from before plus the two new ones).

Seeing an error about a circular dependency?

  • Make sure Total Sales All Countries references [Total Sales] (the measure you created earlier), not FactSales[Sales]. Using the measure name in square brackets is how DAX references another measure.
  • Double check that ALL(DimCountry) references the dimension table name DimCountry, not a column name.

Why am I getting a circular dependency error in my CALCULATE measure?

Build visuals to verify your measures

Measures only come alive when you put them into visuals. You will create one more measure, then build card visuals and a matrix to confirm everything works.

  • Create an Average Sale Price measure by clicking New Measure and entering this formula:
Average Sale Price = AVERAGE(FactSales[Sales]) / AVERAGE(FactSales[Units Sold])

Why is this a measure and not a calculated column?

A calculated column computes a value for every row in the table and stores it permanently. A measure computes a value on the fly based on the current filter context (whichever slicers, rows, or columns are active in your visual).

Average Sale Price needs to recalculate depending on whether you are looking at one country, one product, or the whole dataset. That makes it a measure. Interviewers often ask about this distinction, so it is worth remembering: if the value changes with context, use a measure.

Now build the visuals that put your measures to work.

  • Switch to Report view by clicking the bar chart icon at the top of the left sidebar.
  • Click on an empty area of the canvas.
  • In the Visualizations pane, click the Card visual (the icon that looks like a single number tile).
  • Drag Total Sales from the Fields pane into the card's Fields well.

You should see a card displaying a sales total number on the canvas.

  • Click on an empty area of the canvas again to deselect the first card.
  • Create a second Card visual and drag Profit Margin % into its Fields well.

This card displays the overall profit margin as a decimal. You will format it as a percentage later.

  • Click on an empty area of the canvas to deselect.
  • In the Visualizations pane, click the Matrix visual (the grid icon).
  • Drag DimCountry[Country] into the Rows well.
  • Drag Total Sales into the Values well.
  • Drag Total Profit into the Values well.
  • Drag Profit Margin % into the Values well.
  • Drag Country Sales % into the Values well.

Your matrix now shows each country with its sales, profit, margin, and percentage share. Look at the Country Sales % column. The values for all countries should sum to approximately 1.00 (or 100% if formatted as a percentage). This confirms your CALCULATE + ALL pattern is working correctly.

Country Sales % not adding up to 100%?

  • Click on the Total Sales All Countries measure in the Fields pane and check the formula bar. It should reference ALL(DimCountry), not ALL(FactSales).
  • If every row shows the same percentage, your Country Sales % measure might be dividing [Total Sales All Countries] by [Total Sales] instead of the other way around. The numerator should be [Total Sales] and the denominator should be [Total Sales All Countries].

Help me debug why Country Sales % is not summing to 100% in my Power BI matrix.

✔️ Awesome, I've got everything!

Great. Double check you have seven measures total under FactSales in the Fields pane, and that your matrix shows Country Sales % values summing to approximately 1.00.

ⓧ I'd like to double check all my measures

Here are all seven measures you should have in your FactSales table. Click each measure in the Fields pane and compare the formula bar to these:

Total Sales = SUM(FactSales[Sales])

Total Profit = SUM(FactSales[Profit])

Total Units = SUM(FactSales[Units Sold])

Profit Margin % = DIVIDE([Total Profit], [Total Sales], 0)

Total Sales All Countries =
CALCULATE(
    [Total Sales],
    ALL(DimCountry)
)

Country Sales % =
DIVIDE(
    [Total Sales],
    [Total Sales All Countries],
    0
)

Average Sale Price = AVERAGE(FactSales[Sales]) / AVERAGE(FactSales[Units Sold])

Your core measures are verified and your first visuals are live. Next up, you will write time intelligence measures that compare performance across months and years.

Write Time Intelligence DAX Measures

Your core DAX measures are in place. You have totals, ratios, and a CALCULATE pattern that removes filters to compute share-of-total percentages.

Now it's time to tackle the category of DAX that interviewers test the hardest: time intelligence. These functions compare values across different time periods, like year-to-date accumulations and prior year comparisons. They all rely on the dedicated Date table you marked in Step 3.

In this step, get ready to:
  • Create year-to-date and prior year comparison measures.
  • Write year-over-year and month-over-month growth formulas using VAR.
  • Build a time intelligence matrix to verify your calculations.
Create year-to-date and prior year measures

Two of the most common interview questions involve comparing current performance against a running yearly total and against the same period last year. You will create one measure for each pattern.

  • Select the FactSales table in the Fields pane on the right.
  • Click New Measure on the Home tab and enter this formula:
Sales YTD =
TOTALYTD(
    [Total Sales],
    DimDate[Date]
)

What does TOTALYTD do?

TOTALYTD accumulates Total Sales from January 1 up to whatever date is in the current filter context. It is syntax sugar for CALCULATE with DATESYTD. Both produce identical results, but TOTALYTD is shorter to write.

  • Click New Measure again and enter this formula:
Sales Previous Year =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR(DimDate[Date])
)

What does SAMEPERIODLASTYEAR do?

SAMEPERIODLASTYEAR shifts the date filter back by exactly one year. It is equivalent to DATEADD(DimDate[Date], -1, YEAR). Both require a properly marked Date table, which you set up in Step 3.

You won't see results from these measures yet. You will verify them in a matrix visual at the end of this step.

Seeing an error when entering the formula?

Make sure you selected the FactSales table before clicking New Measure. If the measure was created on the wrong table, right-click it in the Fields pane and select Delete, then try again.

If you see a "column not found" error, confirm the column reference is exactly DimDate[Date] with a capital D. Column references in DAX are case-sensitive.

Help me fix a DAX time intelligence formula error.

Create growth measures with VAR

Growth percentages tell you how performance is changing over time. Interviewers love these because they test whether you can combine multiple measures and use the VAR keyword to write clean, readable DAX.

  • With FactSales still selected, click New Measure and enter this formula:
YoY Growth % =
VAR CurrentSales = [Total Sales]
VAR PriorYearSales = [Sales Previous Year]
RETURN
    DIVIDE(
        CurrentSales - PriorYearSales,
        PriorYearSales,
        0
    )

What does this measure do?

VAR CurrentSales stores the current filter context's total sales. VAR PriorYearSales stores last year's sales by referencing the Sales Previous Year measure you just created.

DIVIDE then calculates the percentage change. Using VAR improves both readability and performance because the engine evaluates each variable only once, even if it is referenced multiple times.

  • Click New Measure and enter the month-over-month baseline:
Sales Previous Month =
CALCULATE(
    [Total Sales],
    DATEADD(DimDate[Date], -1, MONTH)
)
  • Click New Measure one more time and enter the month-over-month growth formula:
MoM Growth % =
VAR CurrentSales = [Total Sales]
VAR PriorMonthSales = [Sales Previous Month]
RETURN
    DIVIDE(
        CurrentSales - PriorMonthSales,
        PriorMonthSales,
        0
    )

What does DATEADD do here?

DATEADD(DimDate[Date], -1, MONTH) shifts the current date filter back by one month. It is the flexible sibling of SAMEPERIODLASTYEAR. DATEADD can shift by DAY, MONTH, QUARTER, or YEAR.

MoM Growth % follows the exact same VAR pattern as YoY Growth %. Once you know one growth formula, you can write any time-period comparison by swapping the baseline measure.

  • Confirm that all five new measures appear under FactSales in the Fields pane: Sales YTD, Sales Previous Year, YoY Growth %, Sales Previous Month, and MoM Growth %.

Missing a measure in the Fields pane?

If a measure did not save, it likely has a syntax error. Click New Measure again and re-enter the formula. Watch for missing commas between DIVIDE arguments and mismatched parentheses.

Help me debug my DAX growth measures.

✔️ Awesome, I've got all five measures!

Great. Make sure all five new measures are visible under FactSales before moving on.

ⓧ I'd like to double check the full measures

Here are all five time intelligence measures you should have created in this step. Each one is entered as a separate New Measure on the FactSales table.

Sales YTD =
TOTALYTD(
    [Total Sales],
    DimDate[Date]
)
Sales Previous Year =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR(DimDate[Date])
)
YoY Growth % =
VAR CurrentSales = [Total Sales]
VAR PriorYearSales = [Sales Previous Year]
RETURN
    DIVIDE(
        CurrentSales - PriorYearSales,
        PriorYearSales,
        0
    )
Sales Previous Month =
CALCULATE(
    [Total Sales],
    DATEADD(DimDate[Date], -1, MONTH)
)
MoM Growth % =
VAR CurrentSales = [Total Sales]
VAR PriorMonthSales = [Sales Previous Month]
RETURN
    DIVIDE(
        CurrentSales - PriorMonthSales,
        PriorMonthSales,
        0
    )
Build a time intelligence matrix

Measures only come alive when you place them in a visual. A matrix is the best way to verify time intelligence because you can expand years into months and watch the numbers accumulate.

Before building the matrix, you need to tell Power BI to display month names in calendar order instead of alphabetical order.

  • Switch to Data view (the table icon on the left sidebar).
  • Select the DimDate table in the Fields pane.
  • Click the Month Name column header to select it.
  • In the Column tools tab on the ribbon, click Sort by Column.
  • Select Month Number from the dropdown.

Why sort by Month Number?

Without this setting, Power BI sorts month names alphabetically (April, August, December...). Sorting by Month Number forces calendar order (January, February, March...) in every visual that uses Month Name.

  • Switch back to Report view (the bar chart icon on the left sidebar).
  • Click an empty area on your report canvas.
  • In the Visualizations pane, select the Matrix visual.
  • Drag DimDate[Year] to the Rows well.
  • Drag DimDate[Month Name] to the Rows well, below Year.
  • Drag Total Sales, Sales YTD, Sales Previous Year, and YoY Growth % to the Values well.
  • Click the expand icon (the forked arrow) on a year row to expand it into months.

You should see months listed in calendar order. Sales YTD should grow larger with each successive month within the same year.

Sales Previous Year should show the matching month from the prior year. It will be blank for the earliest year in the dataset since there is no prior year to compare against.

Sales YTD not accumulating?

If Sales YTD shows the same value as Total Sales for every month, your Date table may not be marked. Go to Data view, select the DimDate table, click Table tools, then click Mark as date table and choose the Date column.

If months appear in alphabetical order instead of calendar order, go back to Data view, select the Month Name column in DimDate, and set Sort by Column to Month Number.

Help me fix my time intelligence matrix in Power BI.

Your DAX library now covers totals, ratios, filter overrides, and time intelligence. Next up, you will arrange everything into a polished, interactive report.

Build the Final Report

Your DAX library is complete. Twelve measures covering totals, ratios, and time intelligence are ready to go.

Now it's time to build a polished, interactive report that puts those measures to work. In this step, you'll create KPI cards, trend charts, slicers for filtering, and a time intelligence matrix with color-coded growth rates.

In this step, get ready to:
  • Add KPI card visuals and page-level slicers for interactivity.
  • Build a trend line chart and a product profitability bar chart.
  • Create a time intelligence matrix with conditional formatting and save your report.
Set up KPI cards and slicers

Your report page currently has verification visuals from the previous steps. You'll clear the canvas and build a polished layout from scratch.

  • Switch to Report view by clicking the bar chart icon in the left sidebar.
  • Select all the existing visuals on the canvas by pressing Cmd+A (macOS) or Ctrl+A (Windows).
  • Press Delete to remove them all.

Why delete the existing visuals?

The card and matrix visuals from earlier steps were quick checks to verify your DAX measures. Deleting them gives you a clean canvas to design a professional layout with proper spacing and alignment.

Don't worry. Your measures and data model are untouched. Only the visuals on the canvas are removed.

KPI cards give report viewers an instant snapshot of the most important numbers. You'll add four cards across the top of the page.

  • In the Visualizations pane on the right, click the Card visual icon (it looks like a single number on a card).
  • Drag the card to the top-left area of the canvas.
  • In the Data pane (also called the Fields pane), expand the FactSales table.
  • Drag Total Sales into the Fields well of the card visual.
  • With the card still selected, click the Format visual icon (paint roller) in the Visualizations pane.
  • Expand Callout value, then set Display units to Auto so the number shows in a readable format like $118M.
  • Repeat the same process to create three more Card visuals, placing them in a row next to the first card.
  • Set the second card's field to Total Profit.
  • Set the third card's field to Profit Margin %. In the Format pane under Callout value, set Display units to None and Value decimal places to 1 so it displays as a clean percentage.
  • Set the fourth card's field to Total Units.

You should see four cards in a row at the top of your canvas, each showing a different KPI value.

Slicers let report viewers filter the entire page by selecting a year or segment. This is what makes a report interactive instead of static.

  • Click an empty area of the canvas to deselect any visual.
  • In the Visualizations pane, click the Slicer visual icon.
  • Drag DimDate[Year] from the Data pane into the slicer's Field well.
  • Position this slicer near the top of the page, beside or below your card visuals.
  • Add a second Slicer visual.
  • Drag DimSegment[Segment] into its Field well.
  • Position this slicer next to the Year slicer.

Click a year in the Year slicer. All four KPI cards should update instantly to reflect only that year's data.

Cards not updating when you click a slicer?

Make sure you dragged the field from DimDate (the dimension table), not from FactSales. The Date column in FactSales is hidden, and slicers need the dimension side of the relationship to filter correctly.

Also check that your relationships in Model view all use single cross-filter direction from dimension to fact.

Why aren't my Power BI slicers filtering the card visuals?

Add a trend line chart and product bar chart

A line chart overlaying current-year sales with prior-year sales is one of the most effective visuals for spotting trends and seasonality. You'll pair it with a bar chart that ranks products by profitability.

  • Click an empty area of the canvas below the cards and slicers.
  • In the Visualizations pane, click the Line chart icon.
  • Drag DimDate[Date] to the X-axis well.
  • Drag Total Sales to the Y-axis well.
  • Drag Sales Previous Year to the Y-axis well as well, so both measures appear as overlapping lines.
  • Resize the line chart so it takes up roughly the left half of the area below the cards.

You should see two lines on the chart. One shows current sales and the other shows the same period from the prior year. Gaps in the prior-year line are normal where no prior data exists.

Why overlay current and prior year?

Plotting both lines together makes year-over-year trends immediately visible. A hiring manager reviewing your report can see seasonal patterns and growth at a glance without needing to switch filters or do mental math.

Now add a bar chart next to the line chart to show which products drive the most profit.

  • Click an empty area to the right of the line chart.
  • In the Visualizations pane, click the Clustered bar chart icon.
  • Drag DimProduct[Product] to the Y-axis well.
  • Drag Total Profit to the X-axis well.
  • Click the three-dot menu (...) in the top-right corner of the bar chart visual.
  • Select Sort axis, then choose Total Profit.
  • Click the three-dot menu again, then select Sort axis and choose Sort descending so the most profitable products appear at the top.

Your bar chart should show products ranked from most to least profitable. Try clicking a year in the Year slicer to see how both charts update together.

Bar chart sorted in the wrong direction?

If the smallest bars appear at the top, you need to toggle the sort direction. Click the three-dot menu (...) on the visual, select Sort axis, and make sure Sort descending is selected.

My Power BI bar chart isn't sorting products by profit correctly. How do I fix this?

Build the time intelligence matrix and save your report

A matrix visual is the best way to present time intelligence measures in a structured, drillable format. Interviewers expect to see year-to-date accumulations and growth rates side by side.

  • Click an empty area of the canvas below the line chart and bar chart.
  • In the Visualizations pane, click the Matrix visual icon.
  • Drag DimDate[Year] to the Rows well.
  • Drag DimDate[Quarter] to the Rows well, placing it below Year to create a drill-down hierarchy.
  • Drag Total Sales to the Values well.
  • Drag Sales YTD to the Values well.
  • Drag YoY Growth % to the Values well.
  • Drag MoM Growth % to the Values well.

Click the expand icon (the forked arrow) on the matrix visual's header to expand all years and reveal the quarterly breakdown.

What should I see in the matrix?

Each year row expands into quarters. Sales YTD should accumulate across quarters within a year. YoY Growth % and MoM Growth % show positive or negative values depending on performance compared to the same period in the prior year or month.

Blank values in the growth columns are expected for the first year in your data, because there is no prior period to compare against.

Conditional formatting turns raw numbers into a visual signal. You'll apply a red-to-green gradient on the YoY Growth % column so negative growth jumps out in red and positive growth shows in green.

  • Make sure the matrix visual is selected.
  • In the Values well of the Visualizations pane, click the dropdown arrow next to YoY Growth %.
  • Select Conditional formatting, then click Background color.
  • In the dialog that appears, set Format style to Gradient.
  • Set the Minimum color to red.
  • Set the Maximum color to green.
  • Click OK to apply the formatting.

The YoY Growth % column in your matrix should now show a gradient from red (lowest values) to green (highest values). This makes performance outliers immediately visible.

Conditional formatting is a portfolio signal

Hiring managers notice when reports use conditional formatting on growth metrics. It shows you think about the end user, not just the data. This small detail separates a finished report from a draft.

Your report is complete. Save it so you have a portfolio-ready file.

  • Press Cmd+S (macOS) or Ctrl+S (Windows) to open the Save dialog.
  • Name the file StarSchema_SalesReport and save it to your Desktop.
  • Confirm you see StarSchema_SalesReport.pbix in the title bar of Power BI Desktop.

Matrix showing blank values for growth columns?

Blanks in the first year are expected because there is no prior year or prior month to compare against. If ALL rows show blanks, check that your DimDate table is marked as a date table. Go to Table tools and verify Mark as date table is set to the Date column.

All my time intelligence measures are returning blanks in the matrix. How do I fix this?

Secret mission

Add a Dynamic Top N Product Filter with RANKX and a Disconnected Slicer

Your report shows every product at once. What if a stakeholder only wants to see the top 5 performers? In this mission, you'll build a dynamic Top N filter using a disconnected slicer table and RANKX.

Before you go

Clean Up Your Resources

This project runs entirely on Power BI Desktop, so there are no ongoing cloud costs or subscriptions to worry about.

Decide whether to keep your file ready for future practice, or remove it from your machine.

Resources you used:

  • Power BI Desktop (installed application).
  • StarSchema_SalesReport.pbix file containing your star schema model, DAX measures, and report visuals.
  • Financial Sample dataset (built into Power BI Desktop, no external file).

Keep everything running

No action needed. Choose this if you plan to keep practicing DAX or want to reference your star schema model in future projects.

  • Your StarSchema_SalesReport.pbix file stays wherever you saved it.
  • Power BI Desktop remains installed and ready for your next project.
  • Consider duplicating the file before experimenting further, so you always have a clean reference copy.

Pause - I'll come back to this later

Keep your file but free up any system resources. Since Power BI Desktop only uses resources while it is open, closing the application is all you need.

  • Save your StarSchema_SalesReport.pbix file if you have not already.
  • Close Power BI Desktop to free up memory.
  • When you are ready to return, double-click the .pbix file to reopen it with your full model, measures, and report intact.

Delete - I don't want to use this again

Remove all project files from your machine. This cannot be undone.

  • Close Power BI Desktop.
  • Delete the StarSchema_SalesReport.pbix file from your computer.
  • If you want to uninstall Power BI Desktop, open Settings, select Apps, find Microsoft Power BI Desktop, and click Uninstall.

Mission accomplished

That's a Wrap!

Nice work! You just built a complete star schema sales analytics model from a flat dataset, wrote a library of interview-level DAX measures, and created an interactive report that puts them all to work.

This is exactly the kind of artifact hiring managers look for in take-home assessments and PL-300 exam prep.

You've learned how to:

  • Transform a denormalized flat dataset into a proper star schema with fact and dimension tables using Power Query, complete with key columns and merge-based lookups.
  • Write 12 DAX measures covering the most commonly tested interview patterns: CALCULATE with ALL for filter overrides, TOTALYTD, SAMEPERIODLASTYEAR, YoY growth, MoM growth, and DIVIDE for safe division.
  • Build an interactive report with KPI cards, a current-vs-prior-year trend line chart, a profitability bar chart, and a time intelligence matrix with conditional formatting.
  • Secret Mission: Implement a dynamic Top N product filter using a disconnected slicer table, RANKX, and SELECTEDVALUE.

Ready to quiz yourself?