3 Ways to Get data for Previous Week in Power BI

[djurecicK2]

Recently, a colleague asked me for some assistance with getting data from the previous week in Power BI. I had to think about it for a minute because there are now at least 3 different ways to do it!

For these examples, let’s assume a model with a sales fact table and a related date table which has date, year, week number, and start of week fields. We will also start with a Sales measure as Sales  = SUM(FactSales[SalesAmount])

Simplified Data Model

Starting Visual

1. The original way

Traditionally, we had to create a measure like the one below to get data from a prior week in a visual showing sales by week. This measure identifies the current year and week number based on row context, then subtracts 1 from the week number to get the week number for the prior week. Then it calculates the prior week sales amount using All(DimDate) to escape the current row context and adds DimDate[Year] = SelectedYear and DimDate[WeekNumber] = PriorWeekNumber to filter to the week we are looking for. There is additional logic that could be added to cross calendar years, but that is not included here for the sake of simplicity.

SalesAmountPriorWeek =

VAR SelectedWeekNumber =

    SELECTEDVALUE ( DimDate[WeekNumber] )

VAR PriorWeekNumber = SelectedWeekNumber - 1

VAR SelectedYear =

    SELECTEDVALUE ( DimDate[Year] )

VAR PriorWeekSales =

    CALCULATE (

        [Sales],

        ALL ( DimDate ),

        DimDate[Year] = SelectedYear,

        DimDate[WeekNumber] = PriorWeekNumber

    )

RETURN

    PriorWeekSales

Adding the above measure to the starting visual gives us the following result:

2. The new way with Enhanced DAX Time Intelligence (Preview)

Enhanced DAX Time Intelligence was just announced at FabCon Europe and is now in Preview. It must be enabled before use. To enable on Power BI Desktop, go to Options and settings > Options > Preview features and enable “Enhanced DAX Time Intelligence”. You will then be prompted to restart Power BI Desktop.

Now that the feature is enabled, we need to define a calendar. Select the DimDate table in the Data pane, then select “Calendar Options” from the Modeling or Table tools tab in the ribbon.

From the next window, select “New calendar”. Also feel free to Mark as date table if you have not already done so.

You will be prompted to enter a calendar name and to identify relevant categories. Let's name the calendar "Calendar1" and then identify the Year and Week of Year columns like so:

Click “Validate data” to ensure that the calendar is set up correctly, then “Save and close” and “Done” to begin using the new calendar.

Now that the new calendar “Calendar1” has been created, we can use it in a measure like so:

SalesAmountPriorWeek New =

CALCULATE ( [Sales], ALL ( DimDate ), PREVIOUSWEEK ( 'Calendar1' ) )

Notice that we still need to use All(DimDate), but the rest of the measure is much simpler and more intuitive than the first example! PREVIOUSWEEK is a new DAX function specifically designed to work with enhanced time intelligence. Adding the new measure to the starting visual gives the following result:

3. The new way with Visual Calculations (Preview)

 Visual Calculations are a relatively new feature in Power BI designed to make it easier to get results without having to know complicated DAX like in the first example. They can only access data inside a single visual and do not interact with the semantic model at all. Additionally, they are not stored inside of the semantic model like traditional measures. Visual Calculations are also a preview feature, but they have been enabled by default in Power BI Desktop since September 2024. To create a new Visual Calculation, select “New Visual Calculation from the Modeling tab in the ribbon, or click on the … in the upper right of the visual.

Or

To get what we need, use the formula below:

Prior Week Sales = NEXT([Sales])

Using NEXT might seem a little counterintuitive here, but since the visual is sorted in descending order, the next row represents the prior week. Once a Visual Calculation is created, it is automatically added to the visual like below.

The numbers look good, but they are not formatted as currency in the Visual Calculation column. To format, go to “General” in the Visualizations Pane, then select “Data format” and choose the appropriate column and formatting options.

Something else you might notice is that there is no value for the last row. This is because a Visual Calculation can only reference data that is on the visual. Since there is no next row, there is no way for the visual calculation to get data for the prior week.

Conclusion

In Power BI there are several options for getting values from prior weeks. New preview features make it easier to get the values you need without using complex DAX. In addition, the examples above can all be modified to work with a fiscal calendar.