Forum Discussion

Lili304's avatar
Lili304
New Member
3 years ago
Solved

Need help with DAX previous quarter

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!

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

     

  • Footer1700's avatar
    Footer1700
    New Member
    1 year ago
    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])