Forum Discussion
Add custom column with previous values
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.
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.
Hello, thanks for you reply, although I have to confess that I don't understand this decompresses binary data part. I tried to use the solution @Greg_Deckler posted, but so far I haven't been able to figure how to make it work. Perhaps you could tell me what part I'm missing.
Forgot to mention that the values you see in the Previous columns are there by requesstt of @Greg_Deckler in order to let him know what was the values I am trying to calculate.
Hi ero1
Thanks for your quick reply, if you want to use calculated column.
You can try the following calculated column.
Previous Month = VAR lastmonth = EOMONTH ( Table1[Date], -1 ) RETURN CALCULATE ( SUM ( Table1[Valor] ), ALLEXCEPT ( Table1, Table1[City] ), EOMONTH ( Table1[Date], 0 ) = lastmonth )Previous year = VAR lastyear = EOMONTH ( Table1[Date], -12 ) RETURN CALCULATE ( SUM ( Table1[Valor] ), ALLEXCEPT ( Table1, Table1[City] ), EOMONTH ( Table1[Date], 0 ) = lastyear )Output
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.
I'm new and terrible at this. Haven't been able to make your suggestion or Greg's work. The worst part, is that I have no clue as to why. I've checked, recheked and made adjustments to both codes and keep getting "Token Eof expected".
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.