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

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