Forum Discussion
Filter values based on arithmetic operations
Hi everyone,
I'm working with PowerBI and I'm trying to get the variation of Data from one year to another of my quantities. The problem is that my years aren't in Date (so I can't use SAMEPERIODLASTYEAR or PREVIOUSYEAR and I can't change it because I use DirectQuery) but in integer. That's why I tried to use a measure filtered with an arithmetic operation, however it didn't work.
Here my code:
Hi Anonymous - you can revise your measure to ensure it handles the year-over-year comparison
VarInPerc2 =
VAR SelectedYear = SELECTEDVALUE(Gastos[control_year])
VAR CurrentYearQty = CALCULATE(
SUM(Gastos[qty]),
Gastos[control_year] = SelectedYear
)
VAR PreviousYearQty = CALCULATE(
SUM(Gastos[qty]),
Gastos[control_year] = SelectedYear - 1
)
RETURN
IF(
NOT ISBLANK(PreviousYearQty), // Make sure PreviousYearQty exists
DIVIDE(CurrentYearQty - PreviousYearQty, PreviousYearQty, 0), // Calculate the percentage difference
BLANK() // If there's no previous year, return BLANK()
)Hope this helps and get the year-over-year percentage variation of your quantities even when control_year
2 Replies
Hi Anonymous - you can revise your measure to ensure it handles the year-over-year comparison
VarInPerc2 =
VAR SelectedYear = SELECTEDVALUE(Gastos[control_year])
VAR CurrentYearQty = CALCULATE(
SUM(Gastos[qty]),
Gastos[control_year] = SelectedYear
)
VAR PreviousYearQty = CALCULATE(
SUM(Gastos[qty]),
Gastos[control_year] = SelectedYear - 1
)
RETURN
IF(
NOT ISBLANK(PreviousYearQty), // Make sure PreviousYearQty exists
DIVIDE(CurrentYearQty - PreviousYearQty, PreviousYearQty, 0), // Calculate the percentage difference
BLANK() // If there's no previous year, return BLANK()
)Hope this helps and get the year-over-year percentage variation of your quantities even when control_year