Add custom column with previous values

[ero1]

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.

[Greg_Deckler]

@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.

[ero1]

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

[v-xinruzhu-msft]

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.

[ero1]

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.

[v-xinruzhu-msft]

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.

[ero1]

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". 

[v-xinruzhu-msft · Accepted solution]

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]

Thanks. I was using the other code in power query, but now that you made me aware of my mistake it works fine. You made my day. Kudos

[ero1]

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.