5 quarters of data and Q5 v Q1 difference

Hi.
I am after thoughts about creating a table which has the latest 5 quarters of data, and an additional column that shows the percentage difference between Q5 and Q1 at the end of the table (using SAMEPERIODLASTYEAR).

My page will have a quarter filter, so for the table I was thibking of having a

MAX for the max quarter, the using a date add function for each of the other 4 quarters (using - 3 months, - 6 months, -9 months etc).

This would give me the individual measures I need and then add an additional column could do a calculation using the max quarter and date add - 12 months.

BUT … is there a quicker or better way than creating so many measures.

Essentially, I want a table like

Q1 Q2 Q3 Q4 Q5 Difference
100 105 106 107 120 20%

Any comments or assistance would be appreciated.
John

Hi John,

First, the number of measures really don’t matter in terms of size and performance because a measure only gets evaluated when used, so in my opinion this shouldn’t be a goal when developing a model.

There are many ways to achieve this by using just 2 measures depending on the evaluation context as illustrated below by the [Total Sales], [Sales % diff] and [Sales % diff v2] measures.

It all depends on what you want or need to show in your report visuals…

Total Sales = 
    SUM( DummyData[Value] )

and

Sales % diff = 
// always calculates the % difference between the first and last defined Q
    VAR FirstQ = 
        CALCULATE( 
            [Total Sales],
            FILTER( ALL( Dates ),
                Dates[QuarterOffset] = -4
            )
        )
    VAR LastQ = 
        CALCULATE( 
            [Total Sales],
            FILTER( ALL( Dates ),
                Dates[QuarterOffset] = 0
            )
        )
RETURN

    IF( ISBLANK( [Total Sales] ),
        BLANK(),
        DIVIDE( LastQ, FirstQ, 0 )-1
    )

OR

Sales % diff v2 =
VAR CQ = [Total Sales]
VAR FirstQ = 
    CALCULATE( 
        [Total Sales],
        DATEADD( Dates[Date], -4, QUARTER )
    )
VAR Result = DIVIDE( CQ, FirstQ )

RETURN

IF( ISBLANK( Result ), BLANK(), Result -1 )

.
For more on DATEADD and SAMEPERIODLASTYEAR see

Here’s an example file:
eDNA Forum - first- lastQ diff.pbix (94.9 KB)

I hope this was helpful.

3 Likes

Hi Melissa,

Superb response, very helpful. Thanks so much for going to the extra effort you did (like spreading the data over a few days in the month) and also attaching the .pbix.

Nice calendar by the way. The added time calculation columns are amazing.

I assume Dates[QuarterOffset] is from today’s date, and will therefore not take into consideration the current filter context, and therefore if you wanted to take in the filter context your would use the in-built time intelligence of QUARTER (your second option).

InsertQuarterOffset = Table.AddColumn(InsertMonthOffset, "QuarterOffset", each ((4 * Date.Year([Date])) +  Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(DateTime.FixedLocalNow()))) +  Date.QuarterOfYear(Date.From(DateTime.FixedLocalNow())))),

Yes this moves through time as the Dates table Query get’s refreshed, it’s dynamic in the sense that all offsets equal to 0 are “now”, negative are in the past and positive in the future.
.

No not really, the changing filter context in the [Sales % diff] measure is due to the use of CALCULATE in combination with FILTER and ALL. The Dates[QuarterOffset] just makes the DAX required to calculate this value so much easier.

For more on CALCULATE, please start here:

BTW the [Sales % diff v2] shows 20% for the last QuarterInCalendar and will show another value for the next QuarterInCalendar when more data has become available in your model…

Understood, thanks very much for your contribution and effort.
The offset solution is very elegant.
Regards
John