Power Query help with finance

[jo123456]

I have a table like the one below 

region industry year value

North America farming 2019 400

Europe carpentry 2020 600

North America farming 2020 700

Europe capentry 2021 800

North America farming 2021 400

In dax 

FYQ1 =

VAR CurrentYear = 'table'[Year]

VAR EarlierYear = CurrentYear - 1

VAR EarlierYearValue =

    CALCULATE(

        SUM('table'[value]),

        FILTER(

            'table',

             'table'[region] = EARLIER( 'table'[region]) &&

              'table'[industry] = EARLIER( 'table'[industry]) &&

             'table'[Year] = EarlierYear

        )

    )

RETURN

    EarlierYearValue * 0.25

to get a desired column value where if region,industry match then I need last year's value to be multiplied with .25 to return a new value. How do I do this in powerQuery without this error  cyclic reference was encountered during evaluation? Please help

[Anonymous · Accepted solution]

Hi @jo123456 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ssvKslQ0lFyzE0tykxOVEhLLMrNzEsHihgZGFoCKRMDA6VYnWgl19Ki/IJUoEByYlFBal5JUSVYjZEBkDKDqsFnGFihOQHDDIGUBTGGGcJcFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [region = _t, industry = _t, year = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"region", type text}, {"industry", type text}, {"year", Int64.Type}, {"value", Int64.Type}}),
    Custom1 = Table.AddColumn(#"Changed Type","Custom",each Table.SelectRows (#"Changed Type",(x) =>x [region]=[region] and x[industry]=[industry] and x[year]=[year]-1)),
    #"Expanded Custom" = Table.ExpandTableColumn(Custom1, "Custom", {"value"}, {"value.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Custom", "FYQ1", each [value.1]*0.25),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"value.1"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"region", Order.Ascending}})
in
    #"Sorted Rows"

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.

[jo123456]

note my current one is in dax