% Change over user selected year columns

[ChrisIrvin]

Hi all,

First time poster! I'd appreciate any help you can give me with this question...

I need to create a dynamic matrix and line chart that shows the percentage change over selected year columns. Example below.

There are 10 years in the report and a user can select any combination of years. The selected years need not be contiguous, but are displayed in order (L to R). If a user adds more years, the table recalculates to reflect all possible % changes. If only 1 year is selected you'd see a blank table/chart. I think this means that a date table may not be appropriate as I want the calculation to work over non-contiguous years.

The result I'm looking for is exactly the same as if you used a pivot table to show % Difference From (previous).

So from this:

Category	2020	2022	2023
A	405379	338089	411576
B	263250	229064	279059
C	19864	11037	15044
D	90307	43171	125443
E	30593	17600	29115
Grand Total	809393	638961	860237

I want this:

Category	2020	2022	2023
A		-17%	22%
B		-13%	22%
C		-44%	36%
D		-52%	191%
E		-42%	65%
Grand Total		-21%	35%

And this:

Happy to provide more info.

Many thanks!

Chris.

[gmsamborn · Accepted solution]

Hi @ChrisIrvin 

Maybe try something like this.

Pct Chg = 
VAR _CurrentSelected = [Total Value]
VAR _PreviousSelected =
    CALCULATE(
        [Total Value],
        OFFSET(
            -1,
            ALLSELECTED( 'Table'[Year] ),
            ORDERBY( 'Table'[Year] )
        )
    )
VAR _Logic =
    DIVIDE(
        _CurrentSelected - _PreviousSelected,
        _PreviousSelected
    )
RETURN
    _Logic

[ChrisIrvin]

Hi there - that solution is working super well. However there's one enhancement I'd like if possible - can the earliest year also be shown as a blank column? This is necessary because having the ability to select any combination of years means that the earliest year might not be the year previous to the earliest one displayed. I hope that makes sense!
Thank you.

[gmsamborn]

Hi @ChrisIrvin 

I'm not really sure what you mean.  Can you show me an example?

[ChrisIrvin]

Perfect! Thank you! Highly appreciated! 😎

[danextian]

This is a good use of OFFSET. Short, easy to understand and fast.

[CoreyP]

I'm not gonna lie... this took me absolutely wayyy longer than it probably should have. But, it was a bit tricky. You do need a date dimension table. IMHO, if a date exists anywhere in your model, you should always have a date dimension table in Power BI. 

Delta = 
VAR _currenttotal = [Total Value]
VAR _previoustotal = 
CALCULATE( 
    [Total Value] , 
        'Date'[Date].[Year] = 
            MAXX( 
                FILTER( 
                    CALCULATETABLE( 
                        VALUES( 'Date'[Date].[Year] ) , 
                        ALLSELECTED('Date'[Date].[Year] ) 
                                ) ,
                        'Date'[Date].[Year] < MAX( 'Date'[Date].[Year] ) 
                        ) , 
                'Date'[Date].[Year] 
                )
            )
VAR _delta = _currenttotal - _previoustotal
VAR _pct = DIVIDE( _delta , _previoustotal , BLANK() )

RETURN
_pct