Hello, I am trying to calculate montly data from cumulative data and 3 groups of data. In order to do that, I have to find PREVIOUS MONTH VALUE
SO, First of all, I grouped my data.
Secondly, I try to calculate previous month value -> i get error
Thirdly, I am expecting my table. -> again error
I want to make sure, that DATE is in date type (it was in date format before expansion of table, but after the expansion it became other). Any suggestions why errors? What to do with data format? Maybe other ways to calculate previous value? ONLY POWER QUERY. In DAX I achieved desired results simply, but I need to manipulate data in excel.
Almost all my code from advanced editor in power query:
<....>
#"Inserted Merged Column" = Table.AddColumn(#"Removed Columns", "Data", each Text.Combine({[Mėnuo], Text.From([Metai], "lt-LT")}, "#(tab)"), type text),
#"Changed Type" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"Data", type date}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Metai", "Mėnuo", "Mėnesio nr.", "Mokestis", "Tipas", "Data"}, "attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Reikšmė"}, {"attribute", "Kategorija"}}),
sorted_data = Table.Sort(#"Renamed Columns", {{"Data", Order.Ascending}}),
// Step 2: Group data by category and type
grouped_data = Table.Group(#"sorted_data", {"Kategorija", "Tipas", "Mokestis"}, {{"Data", each _, type table}}),
// Step 3: Add custom column to each group that calculates previous month value
with_prev_value = Table.TransformColumns(grouped_data, {
{"Data", each Table.AddColumn(_, "Previous Month Value",
each let
prev_month = Date.AddMonths(Date.FromText([Data]{0}[Data], "yyyy-MM-dd"), -1),
prev_month_values = Table.SelectRows([Data], each [Data] = prev_month)
in if List.IsEmpty(prev_month_values) then null else List.Sum(prev_month_values[Value])
), type table}
}),
// Step 4: Expand the "Data" column to get the final table
final_data = Table.ExpandTableColumn(with_prev_value, "Data", {"Previous Month Value", "Data", "Value"}, {"Previous Month Value", "Data", "Value"}),
// Step 5: Add a new column that converts the Data column to a date data type
with_date_column = Table.AddColumn(final_data, "Date", each Date.FromText([Data], "yyyy-MM-dd"))
in
with_date_column
