Forum Discussion

ImPalaBI's avatar
ImPalaBI
Helper I
6 years ago
Solved

Week continuity between years

Hi! I have week to week comparison in a formula. It was working fine until week 52. Now that we changed year, I should compare week 1, to week 52. But I cant figure out how to do it. Im not using ...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi ImPalaBI ,

    I created  a calculated column like this:

    Column = 
VAR x = COUNT(Sheet3[Semana]) + 1
RETURN
CALCULATE(
    SUM(Sheet3[Stock]),
    FILTER(
        Sheet3,
        Sheet3[Semana] = 5-EARLIER(Sheet3[Semana])
    )
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

amitchandak's avatar
amitchandak
Super User
6 years ago

Can you share the current formula

  • ImPalaBI's avatar
    ImPalaBI
    Helper I
    6 years ago

    The main formula is:

    Stock LW =
    VAR SemanaActual = SELECTEDVALUE(agile[Semana.M])

    RETURN

    SUMX(
    FILTER(ALLEXCEPT(agile;agile[Categoría];agile[Marca];agile[Modelo]);
    agile[Semana.M] = SemanaActual -1 );
    [SUM Stock])

     

    That gives me the stock, of the current week - 1 (SemanaActual meand CurrentWeek in Spanish)

    Semana.M is a value filed manually.

     

    From there, I get the current week value with this formula:

    SUM Stock = SUM(agile[Stock])

     

    And then, I divide them to get the difference:

    Stock Dif % Sem a Sem =
    DIVIDE(
    [SUM Stock]-[Stock LW];
    [Stock LW];
    "0")

     

    • v-lionel-msft's avatar
      v-lionel-msft
      Community Support
      6 years ago

      Hi ImPalaBI ,

      I created  a calculated column like this:

      Column = 
VAR x = COUNT(Sheet3[Semana]) + 1
RETURN
CALCULATE(
    SUM(Sheet3[Stock]),
    FILTER(
        Sheet3,
        Sheet3[Semana] = 5-EARLIER(Sheet3[Semana])
    )
)

       

      Best regards,
      Lionel Chen

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.