Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
iberapb
Regular Visitor

From cumulative to per month yeach year many categories query

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

0 REPLIES 0

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors