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 date, because the week is filled manually (because sometimes data from week 20, is filled on week 25 for example)

 

So my formula is to compare MAX week, vs MAX week -1

 

Should I index the weeks? Any other idea on how to solve this?
And any guide on how to do it using time intelligence would be also appreciated, since maybe that is the proper way to do it.

 

Many thanks in advance! 

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.

     

3 Replies

    • 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.