Filter values based on arithmetic operations

[julien310356]

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: 

VarInPerc2 =

VAR SelectedYear = SELECTEDVALUE(Gastos[control_year])

VAR CurrentYearQty = CALCULATE(

    SUM(Gastos[qty]),

    FILTER(Gastos, Gastos[control_year] = SelectedYear)

)

VAR PreviousYearQty = CALCULATE(

    SUM(Gastos[qty]),

    FILTER(Gastos, Gastos[control_year] = (SelectedYear - 1))

)

RETURN

IF( 

    PreviousYearQty = 0, //Case when it is my first year 

    BLANK(), 

    DIVIDE(CurrentYearQty, PreviousYearQty, BLANK())  

)

Any idea to help?

[rajendraongole1 · Accepted solution]

Hi @julien310356 - 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

[rajendraongole1 · Accepted solution]

Hi @julien310356 - 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

[julien310356 · Accepted solution]

It works. Thanks a lot