Forum Discussion

jo123456's avatar
jo123456
Helper I
2 years ago
Solved

Power Query help with finance

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
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.