Others

How to Create Power BI Columns from Previous Weeks' Values

Chanuka Geekiyanage Oct 9, 2026 870 views

Question details

The user wants to calculate and create Balance, Capacity, and Weekly Picks columns in Power BI using data from both the current week and prior weeks, alongside information pulled from a related table.

How to Create Power BI Columns from Previous Weeks' Values

Product: Power BI
Device & OS: not provided
Scenario: Calculating week-over-week metrics and rolling balances dynamically based on related tables and historical weekly data.
Observed behavior: Requires a reliable method to reference the previous week's data and merge it with current data across different tables without manual data entry.

Before you start

Ensure your Power BI data model has a dedicated, continuous Date or Week table that is properly related to your main data tables before writing time-based calculations.

Solution 1Recommended

Use DAX CALCULATE and Time-Intelligence Functions

Create dynamic measures using Data Analysis Expressions (DAX) rather than static calculated columns to flexibly evaluate previous weekly metrics.

To compare or calculate data week-over-week, Power BI relies on time-intelligence functions. Because your calculation depends on previous weeks and data from another table, building relationships in your data model is critical.

Establish a Date Table

Create a continuous Date table in your Power BI model. Mark it as a 'Date Table' and establish an active relationship between its Date column and the date column in your main fact table.

Write the DAX Measure for Prior Week

Use the CALCULATE function combined with a time-intelligence function. For example: `Prior Week Value = CALCULATE(SUM(FactTable[Value]), DATEADD(DateTable[Date], -7, DAY))` to fetch the exact value from seven days ago.

Reference Data from Another Table

If you need to pull Capacity or Weekly Picks from a separate lookup table, use the `RELATED()` function within your DAX calculation to bring that related row context into your formula.

Calculate the Balance

Create a final measure that adds or subtracts your prior week measure from your current week measure (e.g., `Current Balance = [Current Week Value] - [Prior Week Value]`). Add this measure to your visual matrix or table.

Use DAX CALCULATE and Time-Intelligence Functions

Community Support: For highly specific or complex data models, posting your exact table structure and DAX attempts to the official Microsoft Power BI Community forums can help you get tailored formula corrections.

Free Microsoft Office alternative

Simplify Weekly Data Analysis with WPS Spreadsheet

If complex DAX formulas and data modeling in Power BI feel too heavy for your routine reporting, WPS Spreadsheet offers an intuitive and powerful way to track weekly balances and capacities using familiar formulas.

1. Import Your Data: Open WPS Spreadsheet and load your weekly dataset directly from CSV or XLSX formats.
2. Summarize with PivotTables: Go to the Insert tab and click PivotTable to quickly group your data by weeks and summarize capacity without writing complex formulas.
3. Calculate Week-Over-Week: Add a simple subtraction formula (e.g., =C3-C2) in an adjacent column to track balances and pick changes instantly.

Easily calculate rolling weekly balances using standard cell references and VLOOKUP100% compatible with Microsoft Excel (.xlsx) file formatsFree and lightweight alternative to heavy business intelligence toolsFamiliar user interface with zero learning curve for seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

Why is my DAX measure returning blanks for the previous week?

This commonly occurs if your Date table is not marked as a 'Date Table' in Power BI, if there are gaps in your date range, or if the relationship between the Date table and the fact table is inactive.

Should I use a calculated column or a measure for weekly balances?

It is generally best practice to use measures for time-based comparisons. Measures evaluate dynamically based on the filters and slicers applied to your report, whereas calculated columns consume memory and are evaluated statically during data refresh.

How do I pull a 'Capacity' value from a different table into my calculation?

As long as there is an active relationship (typically one-to-many) between the two tables in your data model, you can use the RELATED() function in your DAX formula to retrieve the Capacity value for the corresponding row.

Office Quick Solutions