Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
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"
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
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
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
User | Count |
---|---|
48 | |
44 | |
19 | |
14 | |
14 |
User | Count |
---|---|
107 | |
57 | |
29 | |
20 | |
16 |