topic Re: How can I get the values according to another row in the table in DAX Commands and Tips

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

yj1111 — Thu, 16 Nov 2023 13:54:10 GMT

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.

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

mlsx4 — Thu, 16 Nov 2023 16:21:18 GMT

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"

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

yj1111 — Fri, 17 Nov 2023 08:23:39 GMT

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

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

mlsx4 — Fri, 17 Nov 2023 10:04:35 GMT

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

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

AlienSx — Fri, 17 Nov 2023 11:22:08 GMT

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