populate a column from a previous value

Hi jerryr125 

Use the following formula in the advanced editor window.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1DcyMDIBsgwNlGJ1oIJGMEFjJEFjmKApkiBYuymQZYSuHSxojqYdJAgRc8JmuROK5YamaCpBug3N0VSCBI2NlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProductID = _t, #"Year-Month" = _t, Quantity = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try Table.SelectRows(Source, (x)=> x[ProductID]=[ProductID] and Date.From("01/"& [#"Year-Month"])= Date.AddYears(Date.From("01/"& x[#"Year-Month"]),1))[Quantity]{0} otherwise "")
in
    #"Added Custom"

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 🙂

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

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

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

Hi jerryr125,

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed by the community member for  your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

Hi @jerryr125,

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed by the community members?

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

If we don’t hear back, we’ll go ahead and close this thread. For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum we’ll be happy to assist.
Thank you for being part of the Microsoft Fabric Community.