Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

% Change over user selected year columns

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 c...
Need Help
  • gmsamborn's avatar
    gmsamborn
    2 years ago

    Hi Anonymous 

    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

     

     

CoreyP's avatar
CoreyP
Icon for Solution Sage rankSolution Sage
2 years ago

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