Forum Discussion
Table % Change from Previous Row
- 1 year 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)
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.
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
)
- TCavins1 year ago
Helper V
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.
- TCavins1 year ago
Helper V
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.