Need help with DAX previous quarter

[Lili304]

Hi experts!

Can you please take a look at my issue about calculating quarter over quarter changes below and advise how to fix it?

I have a item table  that contains a list of items and their values and periord. The period column is in text format since it's a combination of year and quarter, like 2021 Q1. I also have a look up table that has 2 columns: the first column is same as the period column in the item table, and the 2nd column shows the previous quarter. For example, if the column 1 row 1 has 2021 Q2; the column 2 row 1 will be 2021 Q1. The tables are linked by Period.
I want to create a time series to show the quarter-over-quarter item value changes.

If the value for a item in 2021 Q2 is 100,000, and the value for the same item in 2021 Q2 is 150,000, then the change will be 50,000 (Q2 value - Q1 value). I want to do the same for all the 200 items in 5 years.

I create a measure called Previous Values to show the values for the previous quarters. Then I want to generate a table visual to show the period column, current quarter value, and the previous quarter value

here is my DAX: Previous Values = CALCULATE([Total values], PREVIOUSQUARTER(Item([Previous quarter]) 

But it doesn't work. It doesn't show the previous quarter value in the table. Any ideas? Thanks so much!

[Footer1700]

Setting date filters in the filters column tools is a simple way to achieve a Prior QTR end value.

In the code

    'Table 1' is my data table

     Valuation Date is a column within the table that is the date for the data row

     Acct Name is for various accounts I have within the table

The filter code is as follows:

FILTER('Table 1',

''Table 1''[Acct Name] = EARLIER(''Table 1''[Acct Name])

'Table 1'[Valuation Date].[QuarterNo]= EARLIER('Table 1'[Valuation Date].[QuarterNo])-1

&& 'Table 1'[Valuation Date].[Year]= EARLIER('Table 1'[Valuation Date].[Year])

[Anonymous · Accepted solution]

Hi @Lili304 ,

Here I create a sample to show you how to achieve your goal.

My Sample:

Data Model:

Measure:

Previous = 
VAR _PREVIOUS_QUARTER =
    CALCULATE (
        MAX ( DimPeriod[Period] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Item] ),
            'Table'[Period] < MAX ( DimPeriod[Period] )
        )
    )
VAR _RESULT =
    CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Item] ),
            'Table'[Period] = _PREVIOUS_QUARTER
        )
    )
VAR _MAXPERIOD =
    MAX ( 'Table'[Period] )
RETURN
    IF ( SELECTEDVALUE ( DimPeriod[Period] ) > _MAXPERIOD, BLANK (), _RESULT )

Diff = SUM('Table'[Values]) - [Previous]

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Lili304]

Thank you Rico!