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 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'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)

6 Replies

    • TCavins's avatar
      TCavins
      Helper V
      11 months ago

      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's avatar
        FBergamaschi
        Super User
        11 months ago

        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'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)