How to Create a Weekly Report in Power BI

Creating a weekly report in Power BI can transform your Monday mornings from a frantic data scramble into a calm, insightful review. Instead of spending hours exporting CSVs and wrestling with spreadsheets, you can build a dynamic, automated report that updates itself. This guide will walk you through setting up a proper date table, writing the necessary DAX measures to track weekly performance, visualizing the data, and automating the entire process.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Why Building Weekly Reports Manually Is a Trap

If you're still creating weekly reports by hand, you're familiar with the Monday routine. You download data from five different platforms, paste it all into a massive spreadsheet, fix a few formatting errors, build some pivot tables, and email a screenshot of a chart to your team - who immediately ask for a different date range. By the time it's done, half your day is gone.

This manual process isn't just time-consuming, it's also prone to mistakes. A simple copy-paste error can throw off your entire analysis, leading to bad decisions based on bad data. The beauty of Power BI is its ability to connect directly to your data sources, allowing you to build a report once and have it refresh automatically. You put in the work upfront to create a reliable, "set-it-and-forget-it" dashboard that serves you fresh insights every week without any manual effort.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 1: Get Your Data Model Ready for Weekly Reporting

Before you can analyze weekly data, you need a solid foundation. The single most important part of any time-based analysis in Power BI is a dedicated date table. A date table acts as a master calendar for your report, allowing you to slice and dice your data by year, quarter, month, week, and day with ease.

Creating a Standard Date Table

While you might have a date column in your sales or marketing data, it’s not enough. You need a separate, complete calendar table with one row for every single day within your desired date range. The easiest way to create one is by using a simple DAX formula.

1. In Power BI Desktop, navigate to the Data view (the table icon on the left).
2. From the top ribbon, select the Table tools tab and click on New table.
3. Enter the following DAX formula into the formula bar and press Enter:

Date Table = 
CALENDAR(DATE(2022, 1, 1), DATE(2024, 12, 31))

This creates a simple one-column table named "Date Table" with a continuous list of dates from January 1, 2022, to December 31, 2024. Adjust the start and end dates to match the timeframe of your data.

Next, let's add some columns to this table that will be incredibly useful for our weekly report. With the "Date Table" still selected, create new columns using the New column button.

• Year:
• Month Name:
• Month Number:
• Week Number:
• Day of Week Name:
• Day of Week Number:

After adding these columns, go back to the Data view, select your "Day of Week Name" column, and in the Sorting ribbon, sort it by the "Day of Week Number" column. This ensures that your charts will correctly display "Monday, Tuesday, Wednesday..." instead of sorting alphabetically.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Connecting Your Date Table

The last step in setting the foundation is critical: you must connect your new date table to your other data tables (like a Sales or Ad Spend table).

1. Switch to the Model view (the relationship icon on the left).
2. You will see boxes representing your data tables. Find the column in your main data table (e.g., 'Order Date' in your sales table) and the [Date] column in your "Date Table".
3. Drag the 'Order Date' field from your sales table and drop it directly onto the [Date] field of your "Date Table".

A line will appear between the two tables, showing they are connected. This "one-to-many" relationship is what allows your date table to filter your sales data correctly. This step is non-negotiable for time intelligence functions to work properly.

Step 2: Building Your Core Weekly Measures with DAX

With our data model in place, it’s time to create the calculations (called "measures" in DAX) that will power our report. Measures are formulas that are calculated on the fly based on the context of your report (e.g., filters, slicers, visuals).

First, create a basic measure for your primary metric. Let's assume we're tracking Sales. In the Reports view, right-click on your sales table and select New measure and enter this formula:

Total Sales:

Total Sales = SUM('Sales Data'[Revenue])

Now, let's build time-based measures that reference this base measure.

Calculating "This Week's" Data

We need a measure that only shows performance for whatever the current week happens to be. This formula always looks for data in the current week:

Total Sales This Week = 
CALCULATE([Total Sales], 
   FILTER(ALL('Date Table'), 
      'Date Table'[Week Number] = WEEKNUM(TODAY(), 2) && 
      'Date Table'[Year] = YEAR(TODAY())))

This measure does the following:

• CALCULATE: This DAX function allows you to modify the filter context of measures or tables.
• FILTER: It narrows down the data to include only rows that meet certain criteria, in this case, the current week.
• WEEKNUM: This function returns the week number for the given date.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Calculating "Last Week's" Data for Comparison

The logic behind this formula is similar, except you're offsetting the week by minus one to get the last week's data:

Total Sales Last Week =
CALCULATE([Total Sales], 
   FILTER(ALL('Date Table'), 
      'Date Table'[Week Number] = WEEKNUM(TODAY(), 2) - 1 && 
      'Date Table'[Year] = YEAR(TODAY())))

This measure is useful for trend analysis and understanding weekly changes.

Visualizing Your Measures in Your Report

Now that we have our core measures, it's time to visualize them. Let’s make use of Power BI's extensive visualization capabilities.

• Use a line chart to visualize trends over weeks with "Total Sales This Week" and "Total Sales Last Week" as lines.
• Use a card visualization to prominently display "This Week's Total Sales" in your report.
• Ensure that slicers are available for other relevant dimensions like product category, region, etc., so users can slice the data as needed.

With these visualizations, you can easily monitor weekly performance and adjust strategies as necessary.

Automating Your Weekly Report

One of the key benefits of using Power BI is automation. After setting up your report, ensure it updates automatically by connecting it to live data sources and scheduling refreshes. This keeps your insights updated without manual intervention.

To set up a scheduled refresh:

• In Power BI Service, locate your dataset and click on the "Schedule Refresh" tab.
• Set up the frequency, such as daily or weekly, based on your reporting needs.
• Ensure data source credentials are properly configured so the refresh process can access the required data.

Conclusion

Building automated weekly reports in Power BI takes effort initially but pays off significantly in the long run. It transforms the way you handle data, allowing you to focus on insights rather than the tedious process of manual data manipulation. By connecting your data, creating powerful DAX measures, and visualizing the right metrics, you can maintain an efficient and insightful reporting process.

And remember, as you gain more experience, you can continually refine your reports and take full advantage of Power BI's capabilities to make data-driven decisions a seamless part of your workflow.