Forum Discussion

cecitorresHF's avatar
cecitorresHF
Helper I
3 years ago
Solved

Semanas continuas

Hola 

Me gustaria saber si alguien sabe como generar una columna con semanas seguidas 1,2,3 etc, pero no empiece en semana 1 cada vez que el a'o cambia, por ejemplo en este caso en donde del 26/12/22 al 1/1/23 seria la semana completa (53) pero como cambia de a;o el ultimo dia de la semana me cambia a semana 1 de nuevo, hay alguna manera que tome el 1/1/23 como semana 53 y continue como semana 54 etc.?

 

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  cecitorresHF ,

     

    Here are the steps you can follow：

    1. Create calculated column.

    week =
WEEKNUM('Table'[Date],2)
    Column =
RANKX(
    'Table','Table'[Date],,ASC)
    Year =
YEAR('Table'[Date])
    True =
var _table1=
FILTER(
    'Table',    'Table'[Date]=MINX(FILTER(ALL('Table'),'Table'[week]=EARLIER('Table'[week])&&'Table'[Year]=EARLIER('Table'[Year])),[Date]))
var _table2=
ADDCOLUMNS(
    _table1,"rank",
RANKX(_table1,[Date],,ASC))
return
IF(
    'Table'[Date]=
    MINX(
        FILTER(ALL('Table'),
        'Table'[Year]=EARLIER('Table'[Year])&&'Table'[week]=EARLIER('Table'[week])),[Date]),
        MINX(
            FILTER(
               _table2,[Date]=EARLIER('Table'[Date])),[rank])
        ,0)
    Week_Number =
MAXX(
    FILTER(ALL('Table'),
    'Table'[Year]=EARLIER('Table'[Year])&&'Table'[week]=EARLIER('Table'[week])),[True])

    2. Result:

     

     

    Best Regards,

    Liu Yang

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

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    Don't waste your time. Use an external table (for example an Excel sheet in a sharepoint)  that has everything precomputed a couple years in advance.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  cecitorresHF ,

     

    Here are the steps you can follow：

    1. Create calculated column.

    week =
WEEKNUM('Table'[Date],2)
    Column =
RANKX(
    'Table','Table'[Date],,ASC)
    Year =
YEAR('Table'[Date])
    True =
var _table1=
FILTER(
    'Table',    'Table'[Date]=MINX(FILTER(ALL('Table'),'Table'[week]=EARLIER('Table'[week])&&'Table'[Year]=EARLIER('Table'[Year])),[Date]))
var _table2=
ADDCOLUMNS(
    _table1,"rank",
RANKX(_table1,[Date],,ASC))
return
IF(
    'Table'[Date]=
    MINX(
        FILTER(ALL('Table'),
        'Table'[Year]=EARLIER('Table'[Year])&&'Table'[week]=EARLIER('Table'[week])),[Date]),
        MINX(
            FILTER(
               _table2,[Date]=EARLIER('Table'[Date])),[rank])
        ,0)
    Week_Number =
MAXX(
    FILTER(ALL('Table'),
    'Table'[Year]=EARLIER('Table'[Year])&&'Table'[week]=EARLIER('Table'[week])),[True])

    2. Result:

     

     

    Best Regards,

    Liu Yang

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