Table % Change from Previous Row

[TCavins]

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 overthinking the DAX. Can someone help provide the correct DAX to get this? The years could be in order, they could just be 2020 and 2024.

[TCavins · Accepted solution]

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)

[FBergamaschi]

Here

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[TCavins]

When I do the measure, it's not letting me select 'table'[year]. I'm trying measures as it needs to calculate on the fly based on Year or any other column the user may filter on to get units for that year/filter.

[FBergamaschi]

You asked for a column so I created that. If you want a measure

Meas=

VAR CURRENTYEAR = SELECTEDVALUE(TABLE[YEAR])

RETURN

CALCULATE ( SUM (TABLE[QTY] ), TABLE[YEAR] = CURRENTYEAR-1,

REMOVEFILTERS(TABLE[YEAR])

)

If this helped, please consider giving kudos and mark as a solution

@mein replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

)

[TCavins]

Not working for me. It's blank.

The years could be 2020,2021,2022, 2023,2024,2025.   They could be 2020, 2023, 2025 or any other combination that the user has chosen so I don't think I can just say currentYear -1.

[TCavins]

@FBergamaschi So my dataset has Year and Units.  I'm putting them into a Power BI table visual  summing the units to get total by year.   Within the visual, I want to get the % change in total units from the previous row. Filters on the page could make it so the years are not consecutive (2020, 2023, and 2025). I'm using a measure as I want the value to be variable based on what's shown in the visual.