how to calculate growth as column with categories (in query)

[nada]

Hello 

sumx(filter(Table, [city] = earlier([city]) &&[commodity] = earlier([commodity]) && eomonth([date],+1) = eomonth(earlier([date]),0)), Table[Value])

how can i transfer the code in (query)

i want to unpivot the column ot it doesn't work in dax 

Solved: Re: how to calculate growth as column with categor... - Microsoft Power BI Community

[v-jingzhang · Accepted solution]

Hi @nada 

You can try Group By feature to get monthly sum values and then add a custom column to get previous month sales.

1. Group by the first four columns and get Sum of sales into a new Sales column. 

2. Add a custom column with below code. You can modify it accordingly. 

List.First(Table.Column(Table.SelectRows(#"Grouped Rows", (x)=> x[city]=[city] and x[year]=[year] and x[date]=Date.AddMonths([date],-1)),"Sales"), null)

Full code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFTSUfJKzCtNLKoEsVJTUhIzgAxDfUN9qKypqVKsDpFqDQ2MiFdsYYJdbVBmZWIKmlpLMxLUGpOg1hBZrVtqUhG6g42QFFuQoNjQwJgUo01wKIY7GlmxESmKSTIZGMyxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [year = _t, month = _t, city = _t, date = _t, sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"year", Int64.Type}, {"month", type text}, {"city", type text}, {"date", type date}, {"sales", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"year", "month", "city", "date"}, {{"Sales", each List.Sum([sales]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Previous Month Sales", each List.First(Table.Column(Table.SelectRows(#"Grouped Rows", (x)=> x[city]=[city] and x[year]=[year] and x[date]=Date.AddMonths([date],-1)),"Sales"), null))
in
    #"Added Custom"

You can paste above code into a new blank query's Advanced editor to see the detailed steps. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[jennratten]

Hello!  Below are links that explain different methods that could be implemented for something like this, however, determining which is more applicable and how to customize it to fit your needs will be based on what your data looks like.  If you will post a sample of your data I can help further. 

https://www.thebiccountant.com/2018/09/30/memory-efficient-clustered-running-total-in-power-bi/ 

https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-po...