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.

     

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.