Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
ero1
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] )
        )
    )

Can someone please help me solve this. 

Thanks

 

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

Example MoM.png

1 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")

vxinruzhumsft_0-1713507081793.png

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.

 

View solution in original post

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

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.
DateCityValuePrevious Month  ValuePrevious Year  Value
01/01/2020Mexico0.01254  
02/01/2020Mexico0.012160.01254 
01/01/2020Juarez0.01069  
02/01/2020Juarez0.011640.01069 
01/01/2021Mexico0.01199 0.01254
02/01/2021Mexico0.011350.011990.01216
01/01/2021Juarez0.01241 0.01069
02/01/2021Juarez0.011390.012410.01164
01/01/2022Mexico0.01255 0.01199
02/01/2022Mexico0.012290.012550.01135
01/01/2022Juarez0.01070 0.01241
02/01/2022Juarez0.011650.010700.01139
01/01/2023Mexico0.01201 0.01255
02/01/2023Mexico0.011380.012010.01229
01/01/2023Juarez0.01239 0.01070
02/01/2023Juarez0.011430.012390.01165
01/01/2024Mexico0.01263 0.01201
02/01/2024Mexico0.012270.012630.01138
01/01/2024Juarez0.01068 0.01239
02/01/2024Juarez0.011680.010680.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

vxinruzhumsft_0-1713496437798.png

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.

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

vxinruzhumsft_0-1713504809878.png

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

vxinruzhumsft_0-1713507081793.png

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.

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors