Forum Discussion
Add custom column with previous values
I'm in power query trying to add two custom columns. One with the previous values by month and another with the previous values by year. I need these to calculate another two columns with % MoM and % YoY change. I've tried using the following aproach, but have been unsucessfull:
Previous Month =
VAR Current_Date = MAX ( Table1[Date] )
VAR Previous_Date =
CALCULATE (
MAX ( Table1[Date] ),
FILTER (
ALL ( Table1 ),
Table1[Date] < Current_Date
&& Table1[City] = EARLIER ( Table1[City] )
)
)
RETURN
CALCULATE (
MAX ( Table1[Valor] ),
FILTER (
ALL ( Table1 ),
Table1[Date] = Previous_Date
&& Table1[City] = EARLIER ( Table1[City] )
)
)Can someone please help me solve this.
Thanks
I include an example of my table. The one I use has over a thousand rows.
Hi ero1
Did you use the code in power query? The code Greg_Deckler and i offered is used in power bi desktop, if you want to achieve this in power query you need to use the code i offered at 4th message.
In power query, create the following custom column.
Custom column1
=List.Max(Table.SelectRows(#"Changed Type",(x)=>x[City]=[City] and x[Date]=Date.AddMonths([Date],-1))[Value])Custom column2
List.Max(Table.SelectRows(#"Changed Type",(x)=>x[City]=[City] and x[Date]=Date.AddYears([Date],-1))[Value])replace the #"Changed type"with the last step name of your query.(Format:#"xxxxx")
If the solutions @Greg_Deckler and i offered help you solve the provlem, please consider to mark them as solutions
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
ero1 First, that's DAX code and this is the Power Query forum so probably not the right spot. If these are truly calculated columns and not measures and assuming you want DAX, then perhaps this:
Previous Month = VAR Current_Date = [Date] VAR Current_City = [City] VAR Previous_Month = EOMONTH( [Date], -1 ) VAR Previous_Date = DATE( YEAR( Previous_Month ), MONTH( Previous_Month ), DAY( Previous_Month ) ) VAR Result = MAXX( FILTER( Table1, [Date] = Previous_Date && [City] = Current_City ), [Valor] ) RETURN ResultIf not, please post sample data as text and expected results. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Sorry about not being too clear about what i want to do.
Yes, I'm working with power querry in power bi, and need truly calculated columns and not measures. In the table included, my data consists of first three columns and I need power query to add the last two, which are the previous monthly and yearly values. I tried your suggested code, but couldn't make it work. As you already guessed, I'm new to using power bi. Thanks in advance for your help.Date City Value Previous Month Value Previous Year Value 01/01/2020 Mexico 0.01254 02/01/2020 Mexico 0.01216 0.01254 01/01/2020 Juarez 0.01069 02/01/2020 Juarez 0.01164 0.01069 01/01/2021 Mexico 0.01199 0.01254 02/01/2021 Mexico 0.01135 0.01199 0.01216 01/01/2021 Juarez 0.01241 0.01069 02/01/2021 Juarez 0.01139 0.01241 0.01164 01/01/2022 Mexico 0.01255 0.01199 02/01/2022 Mexico 0.01229 0.01255 0.01135 01/01/2022 Juarez 0.01070 0.01241 02/01/2022 Juarez 0.01165 0.01070 0.01139 01/01/2023 Mexico 0.01201 0.01255 02/01/2023 Mexico 0.01138 0.01201 0.01229 01/01/2023 Juarez 0.01239 0.01070 02/01/2023 Juarez 0.01143 0.01239 0.01165 01/01/2024 Mexico 0.01263 0.01201 02/01/2024 Mexico 0.01227 0.01263 0.01138 01/01/2024 Juarez 0.01068 0.01239 02/01/2024 Juarez 0.01168 0.01068 0.01143 Hi,
Thanks for the solution Greg_Deckler provided, and i want to offer some more information for user to refer to.
hello ero1 , based on your description, you can create a blank query and input the following code in advanced editor in power query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZJNCsIwEIXvknWx85OkzRUET1C6kNKFK0EQxNMb0IVvnkIWWXx8b+Yxy5JEx/5MTNKQTvvjsl37Rw6iVnJah07YX0Lrm/h2HO/n2/78EFIbO4DQmtGhMUVbcDDhhR2QYlnZgXN4Q4dxHwUdTNgPB/YxCTtCH2EXpxQJuxChPrMD+/DGDpwjOzoyzVEdHUzYxI5wHzM7Qh+dWF8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, City = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"City", type text}, {"Value", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "PreMonth Value", each List.Max(Table.SelectRows(#"Changed Type",(x)=>x[City]=[City] and x[Date]=Date.AddMonths([Date],-1))[Value])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "PreYearValue", each List.Max(Table.SelectRows(#"Changed Type",(x)=>x[City]=[City] and x[Date]=Date.AddYears([Date],-1))[Value])) in #"Added Custom1"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
