Forum Discussion

TCavins's avatar
TCavins
Helper V
11 months ago
Solved

Table % Change from Previous Row

I have a simple table that has 2 columns.   Year SUM Units   I would like to add a third column that gets the % Change of Sum Units for each row compared to the previous year. I'm likely overthi...
  • TCavins's avatar
    TCavins
    11 months ago

    Here's the solution I developed combining various sources I found. It may not be the most condensed but it works:

    Units % Changed = 
VAR CurrentRowDate = MAX(MYTABLE[Year])
var currentUnits = CALCULATE(
                                SUM(MYTABLE[Units]),
                                FILTER(
                                    ALLSELECTED(MYTABLE),
                                    MYTABLE[Year] = CurrentRowDate))

VAR previousUnits =
    CALCULATE(
        SUM(MYTABLE[Units]),
        FILTER(
            ALLSELECTED(MYTABLE),
            MYTABLE[Year] 
                = CALCULATE (
                    MAX(MYTABLE[Year]),
                    FILTER(ALLSELECTED(MYTABLE), MYTABLE[Year] < CurrentRowDate)
                )
        )
    )
var percentChanged = (currentUnits - previousUnits) / previousUnits 
RETURN IF(ISBLANK(previousUnits), BLANK(),percentChanged)
TCavins's avatar
TCavins
Helper V
11 months ago

Here's the solution I developed combining various sources I found. It may not be the most condensed but it works:

Units % Changed = 
VAR CurrentRowDate = MAX(MYTABLE[Year])
var currentUnits = CALCULATE(
                                SUM(MYTABLE[Units]),
                                FILTER(
                                    ALLSELECTED(MYTABLE),
                                    MYTABLE[Year] = CurrentRowDate))

VAR previousUnits =
    CALCULATE(
        SUM(MYTABLE[Units]),
        FILTER(
            ALLSELECTED(MYTABLE),
            MYTABLE[Year] 
                = CALCULATE (
                    MAX(MYTABLE[Year]),
                    FILTER(ALLSELECTED(MYTABLE), MYTABLE[Year] < CurrentRowDate)
                )
        )
    )
var percentChanged = (currentUnits - previousUnits) / previousUnits 
RETURN IF(ISBLANK(previousUnits), BLANK(),percentChanged)