Previous Quarter Data in non-visual table

[jam17]

I have seen all sorts of tricks for doing this in a table visualization but I want to do this in the Data Table so that when I use a set of sliders to set the Year and Quarter, it will pull the information I need into a set of cards.

What I would like to do is set up my table so that for each row, it also prints the Spend for the previous quarter.  To try to understand how things work, I made the test column to identify/debug the correct previous quarter.

I'm trying then to make another calculated column to pull in the Spend from the same table, but where Proxy Date = test; such that if in row 1, I can see Spend ($824 K) and Previous Spend ($1.2 M).  Everything I am trying either is not letting me pull a single value or is just returning empty cells with no explanation.

Ultimately what I'd like to do is make it so in this visual, I can have the two values of Current and Previous Quarter side  by side, with the one set of sliders.

[DataNinja777]

Hi @jam17 ,

To achieve your goal of displaying the previous quarter's spend alongside the current quarter's spend, the most effective and dynamic approach in Power BI is to use a DAX measure rather than a calculated column. A calculated column is static and evaluates only when your data model refreshes, meaning it cannot respond to user interactions like slicer selections. A measure, however, is calculated dynamically at query time, making it perfect for interactive visuals that respond to filters.

You can create a new measure that calculates the spend for the quarter immediately preceding the one selected in your slicers. This is accomplished using time intelligence functions that modify the filter context. The DAX formula to create this measure would be:

Previous Quarter Spend =
CALCULATE (
    SUM ( MSL[Spend] ),
    DATEADD ( 'MSL'[Month.Date], -1, QUARTER )
)

This formula works by taking the primary expression, SUM(MSL[Spend]), and applying a modified date filter. The DATEADD function shifts the current date context, defined by your slicer selections, back by one interval of the specified type. In this case, it shifts the context back by one QUARTER, effectively instructing CALCULATE to sum the Spend for the previous quarter's timeframe.

With this new measure created, you can easily build your intended visual. Simply place your original Spend measure in one card visual to represent the "Current Quarter". Then, add a second card visual to the report and place your new Previous Quarter Spend measure in it. When you select a period, such as "Qtr 1" of "2025", the first card will correctly show $1.27M, while the second card will dynamically calculate and display the spend from Q4 2024. This method provides the interactive, side-by-side comparison you're looking for, driven entirely by a single set of slicers.

Best regards,

[johnt75]

I think you're over complicating this. Set up a date table, marked as a date table and then you can create measures like

Spend previous quarter =
CALCULATE ( SUM ( MSL[Spend] ), PREVIOUSQUARTER ( 'Date'[Date] ) )

You can then put this into a card visual, or any other visual, next to the measure for the current spend.

[burakkaragoz · Accepted solution]

Hi @jam17 ,

Great question! To display previous quarter values side by side with current quarter values in a table (not just in a visual), you’ll need a calculated column or measure that “looks up” the Spend for the previous quarter for each row.

Here’s how you can do it with a DAX measure:
A measure is preferable because calculated columns can’t dynamically respond to slicers/filters for Year and Quarter.

Step 1: Create a Previous Quarter Spend Measure

dax

Previous Quarter Spend =
VAR SelectedQuarter = SELECTEDVALUE('MSL'[QuarterNo])
VAR SelectedYear = SELECTEDVALUE('MSL'[Year])
VAR PrevQuarter =
    IF(
        SelectedQuarter = 1,
        4,
        SelectedQuarter - 1
    )
VAR PrevYear =
    IF(
        SelectedQuarter = 1,
        SelectedYear - 1,
        SelectedYear
    )
RETURN
    CALCULATE(
        SUM('MSL'[Spend]),
        'MSL'[QuarterNo] = PrevQuarter,
        'MSL'[Year] = PrevYear
    )

• This measure will return the Spend for the previous quarter for each row in your table.
• Make sure your table has a unique row for each Year + Quarter.

Step 2: Add Both Measures to Your Table Visual

• Put your current Spend measure and the new Previous Quarter Spend measure side by side.
• With your slicers for Year and Quarter, both values will update dynamically.

Notes:

• If you want to show only the current and previous quarter as single cards (not as a table), use the same measure approach but filter your cards to show only the selected and previous quarter.
• If you need more complex logic (e.g., fiscal calendars), let me know!

Let me know if you need a sample PBIX or run into any issues with the formula above!
translation and formatting supported by AI