5 quarters of data and Q5 v Q1 difference

Power BI
1

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

2

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…

first-%20lastQ%20diff%201
first-%20lastQ%20diff%201813Ă—181 11.5 KB 

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
https://forum.enterprisedna.co/t/dateadd-vs-sameperiodlastyear/4928

https://forum.enterprisedna.co/t/the-best-time-comparison-function-dateadd/4927

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

I hope this was helpful.

3 Likes
3

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())))),
4

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…

5

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