How can I get the values according to another row in the table

[yj1111]

I have a table as belows

code    date                     value1   value 2 (calculated column I want to have)

001      2022.11.01           10          10

001      2022.12.01           10          20

001      2023.01.01           20          20

001      2023.02.01           20          20

001      2023.03.01           20          20

...

001      2023.12.01           20          30

001      2024.01.01           30

I want to have a fourth column: value2, and if the month is not 12, then value2 = value1; else value2 = value1 of next year. How should I write the dax function?

Thanks in advance.          

[mlsx4]

Hi @yj1111 

I have got an approach but using Power M:

let
    Origen = Excel.Workbook(File.Contents("C:\ex.xlsx"), null, true),
    Hoja1_Sheet = Origen{[Item="Hoja1",Kind="Sheet"]}[Data],
    #"Encabezados promovidos" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"date", type date}, {"value 1", Int64.Type}}),
    #"Índice agregado" = Table.AddIndexColumn(#"Tipo cambiado", "Index", 0, 1, Int64.Type),
    #"Columna condicional agregada" = Table.AddColumn(#"Índice agregado", "value 2", each if Date.Month([date]) <> 12 then [value 1] else #"Índice agregado"{[Index]+1} [value 1]),
    #"Columnas quitadas" = Table.RemoveColumns(#"Columna condicional agregada",{"Index"})
in
    #"Columnas quitadas"

[yj1111]

Hi, 

thanks for your reply. It works with only one code number. But If I have more code number, it does not work.

Do you have an idea? (see the example below) Thanks in advance!

code    date                     value1   value 2 

001      2022.11.01           10          10

001      2022.12.01           10          20

001      2023.01.01           20          20

001      2023.02.01           20          20

001      2023.03.01           20          20

...

001      2023.12.01           20          30

001      2024.01.01           30

...

001      2024.12.01           30         0 (not 50)

002      2022.01.01           50

[AlienSx]

Hello, @yj1111 then you could group by code and apply @mlsx4 's solution to each group. Or try this

let
    Source = your_table,
    f = (tbl as table) =>
        [t = 
            Table.FromColumns(
                Table.ToColumns(tbl) & 
                {List.RemoveFirstN(tbl[value1], 1) & {0}},
                Table.ColumnNames(tbl) & {"v_shift"}
            ),
        v2 = 
            Table.AddColumn(
                t, "value2", 
                each if Date.Month(Date.FromText([date], [Format = "yyyy.MM.dd"])) = 12 
                then [v_shift] 
                else [value1]
            ),
        end = Table.RemoveColumns(v2, "v_shift")][end],
    g = Table.Group(Source, "code", {"t", each f(Table.Sort(_, "date"))}),
    expand = Table.ExpandTableColumn(g, "t", {"date", "value1", "value2"})
in
    expand

[mlsx4]

Ohh you're right! I was thinking about some ideas, but none works.

Probably @AlienSx could help you. He's really good with Power M