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.
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
Result
If 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.
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.