Week continuity between years

[ImPalaBI]

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! 

[amitchandak]

Can you share the current formula

[ImPalaBI]

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 · Accepted solution]

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.