Forum Discussion

ImPalaBI's avatar
ImPalaBI
Icon for Helper I rankHelper 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
Icon for Super User rankSuper User
6 years ago

Can you share the current formula

ImPalaBI's avatar
ImPalaBI
Icon for Helper I rankHelper 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
    Icon for Community Support rankCommunity 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.