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 v...
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.

     

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])