cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## 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] )
)
)

Thanks

I include an example of my table. The one I use has over a thousand rows.

1 ACCEPTED SOLUTION
Community Support

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

Custom column2

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 9
Super User

@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

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor
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

Community Support

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}}),
in

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.

Frequent Visitor

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.

Community Support

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.

Frequent Visitor

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

Community Support

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

Custom column2

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.

Frequent Visitor

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

Frequent Visitor

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.