Column showing prior quarter values per region

[Frenchtom811]

Hi Folks, please see below.  I have quarterly summary financial data by region in the first 3 columns of my table.  I want to create a calculated column that shows the prior quarter financial data per region.  Any idea how this is done?  Seems simple but everything I try results in a prior quarter value that representes all 3 regions data combined.   For reference, tha DAX code I am currently using is 

previousquarter =

CALCULATE (

SUM ( '*Summary Level Data'[Revenue]),

ALL ('*Summary Level Data'),

PREVIOUSQUARTER ('*Summary Level Data'[Date])

)

[lbendlin]

Two comments:

- the use if EARLIER is discouraged.  Use variables or ALLEXCEPT([Region])

Prev QTR Net Rev =
CALCULATE (
SUM ( 'Table'[Net Revenue]),
ALLEXCEPT('Table', 'Table'[Region]),
PREVIOUSQUARTER ('Table'[Date])
)

- Note that PREVIOUSQUARTER gives you the entire period's worth of data. You need additional filters if you want to compare quarter over quarter to date.

[Frenchtom811]

Hello @lbendlin and apologies for the slow reply.  How is ALLEXCEPT better than EARLIER?

Thanks!

[lbendlin]

I don't actually know. EARLIER points to a particular filter context (the one above in the call stack).  ALLEXCEPT _should_ work regardless of context stack position.  You would have to run both queries in DAX Studio to see if they produce different query plans.

EARLIER is a function that was available at the same time as DAX for Power BI was introduced.  Variables and ALLEXCEPT were introduced (much) later.

[v-angzheng-msft]

Hi, @Frenchtom811 

Try to create a measure like this:

_PQ = 
VAR _lastDate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Region] = MAX ( 'Table'[Region] )
                && 'Table'[Date] < MAX ( 'Table'[Date] )
        )
    )
VAR _pq =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Region] = MAX ( 'Table'[Region] )
                && 'Table'[Date] = _lastDate
        ),
        'Table'[Revenue]
    )
RETURN
    _pq

result:

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Frenchtom811 · Accepted solution]

Hello @v-angzheng-msft , @Ashish_Mathur  and @lbendlin !  I actually figured it out.  Please see the below equation, so far I have found no erros in the calculted results.

Prev QTR Net Rev =

CALCULATE (

SUM ( 'Table'[Net Revenue]),

Filter('Table', [Region] = Earlier('Table'[Region])),

PREVIOUSQUARTER ('Table'[Date])

)

[Ashish_Mathur]

Hi,

This should ideally be solved via a measure (not a calculated column formula).  Create a Calendar Table and write calculated column formulas to extract Year and Quarter.  To your visual, drag Year and Quarter from the Calendar Table.  Write these measures:

Total revenue = SUM ( '*Summary Level Data'[Revenue])

Total revenue in previous quarter = calculate([total revenue[,previousquarter(calendar[date]))

Hope this helps. 

[lbendlin]

It's doing what you told it to do. By default the Time Intelligence functions return the data for the ENTIRE period, in your case for the entire previous quarter. If you don't want that you need to add data range filters that limit the date range to "today or earlier, but in the previous quarter".  You can calculate that flag during dataset refresh.