5 quarters of data and Q5 v Q1 difference

Power BI
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