populate a column from a previous value

[jerryr125]

Hi - 

Not sure if this can be done.  

I have the following table in which I would like to create a column from previous values.

Example :

table_abc

ProductID	Year-Month	Quantity
A	01/2024	10
A	02/2024	30
A	03/2024	50
A	01/2025	20
A	02/2025	27
A	03/2025	7
B	01/2024	10
B	02/2024	15
B	01/2025	17
B	02/2025	32

Output (new column prior-year-month-quantity):

ProductID	Year-Month	Quantity	prior-year-month-quantity
A	01/2024	10
A	02/2024	30
A	03/2024	50
A	01/2025	20	10
A	02/2025	27	30
A	03/2025	7	50
B	01/2024	10
B	02/2024	15
B	01/2025	17	10
B	02/2025	32	15

Any thoughts ? Thanks - Jerry

[Abhilash_P]

Hi @jerryr125 ,

You can create a calculated column in power bi desktop with below DAX code

prior-year-month-quantity =
VAR CurrentProduct = table_abc[ProductID]
VAR CurrentDate = DATEVALUE("01-" & table_abc[Year-Month])
VAR PriorYearDate = EDATE(CurrentDate, -12)

RETURN
CALCULATE(
MAX(table_abc[Quantity]),
FILTER(
table_abc,
table_abc[ProductID] = CurrentProduct &&
DATEVALUE("01-" & table_abc[Year-Month]) = PriorYearDate
)
)

[PwerQueryKees]

And another possible solution..

Staring with: (note that the dates are provided as a string).

Using this:

let
    Source = ProductQuantityByMonth,  // replace with your table
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProductID", type text}, {"Year-Month", type date}, {"Quantity", Int64.Type}}),  // The change type interprets the date string as an actual date
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Last-Year-Month Quantity", each  // we add a new column
            let 
                last_year = Table.SelectRows(#"Changed Type",     // get the previous month's row
                    (row) => // a funtion with the current row as a parameter
                        row[ProductID] = [ProductID] // for the same product
                        and row[#"Year-Month"] = Date.AddYears([#"Year-Month"],-1))  // for the previous year
                        [Quantity]  // but only the column [Quantity]
                in 
                    List.SingleOrDefault(last_year)  //  returns the first and only element of the list, null when empty or generarate an error if there are more. You can change this to a List.Sum() if the same month can occur more than once for the same product.
        )
in
    #"Added Custom"

Producing this:

[Nasif_Azam]

Hey @jerryr125 ,

Calculated column that retrieves the quantity from the same ProductID and same Month but in the previous Year.

prior-year-month-quantity =
VAR CurrentProduct = table_abc[ProductID]
VAR CurrentDate = table_abc[Year-Month]
VAR PriorYearDate =
    EOMONTH(CurrentDate, -12)
RETURN
CALCULATE(
    MAX(table_abc[Quantity]),
    FILTER(
        table_abc,
        table_abc[ProductID] = CurrentProduct &&
        table_abc[Year-Month] = PriorYearDate
    )
)

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[AlienSx]

let
    fx_prior_year = (txt) => ((split) => split{0} & "/" & Text.From(Number.From(split{1}) - 1))(Text.Split(txt, "/")),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    data_rec = Function.Invoke(
        Record.FromList, 
        List.Reverse(
            Table.ToColumns(
                Table.Group(Source, "Year-Month", {"x", (x) => Record.FromList(x[Quantity], x[ProductID])})
            )
        )
    ), 
    prior_year = Table.AddColumn(
        Source, 
        "prior_year", 
        (x) => Record.FieldOrDefault(
            Record.FieldOrDefault(data_rec, fx_prior_year(x[#"Year-Month"]), []),
            x[ProductID]), 
            null
    )
in
    prior_year

[Cookistador]

Hi @jerryr125 

I had à similar issue ar à customer, this is how I solved it 

1) transform your year month on sat/year/month,

2) create a column previous year 

Previousyear= Date.AddYears([#"Year-Month"], -1)

3) make a self right join of your table, for your right table select product id and date & for the left one, select productid and preciousyear (the column de added at the previous step)

4) expand the quantity from the merge table and renamme it 

And that's ho you can solve  this issue 🙂

[SamsonTruong]

Hi @jerryr125 ,

To do this in Power Query, please try the following code. Make sure to replace the Source with your actual table source:

let
    Source = PlaceYourSourceHere,
    SplitDate = Table.SplitColumn(Source, "Year-Month", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Month", "Year"}),
    ChangedTypes = Table.TransformColumnTypes(SplitDate, {{"Month", Int64.Type}, {"Year", Int64.Type}, {"Quantity", Int64.Type}}),
    AddDate = Table.AddColumn(ChangedTypes, "Date", each #date([Year], [Month], 1), type date),
    PreviousTable = Table.SelectColumns(AddDate, {"ProductID", "Date", "Quantity"}),
    RenamedPrev = Table.RenameColumns(PreviousTable, {{"Date", "PriorDate"}, {"Quantity", "PriorQuantity"}}),
    AddShiftedDate = Table.AddColumn(AddDate, "PriorDate", each Date.AddMonths([Date], -1), type date),
    MergedTables = Table.NestedJoin(AddShiftedDate, {"ProductID", "Date"}, RenamedPrev, {"ProductID", "PriorDate"}, "PriorData", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(MergedTables, "PriorData", {"PriorQuantity"}, {"prior-year-month-month-quantity"})
in
    Expanded

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

Connect with me on LinkedIn

Check out my Blog

Going to the European Microsoft Fabric Community Conference? Check out my Session