Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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 SAMEPERIODLAS...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    1 year ago

    It works. Thanks a lot

rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    It works. Thanks a lot