Solved: Re: Average based on column name and a date refere...

bdenehy · ‎05-10-2022

Hi,

I have a data source structured as follows.

It lists daily prices for calendar year stock options. Prices are valid up to 1 April for the respective year you would buy for, e.g. prices for CY2008 become zero (0) on 1/4/2008.

I would like to create a daily average price to buy options 4 years in advance (noting that options cease on 1 April as above).

See my diagram below.

I've tried using this as a guide. But I'm finding it hard to consistently enact the logic in blue: when price = 0 or blank, pull the most recent price for Date.Year[Time UTC+10] from the respective column.

The calculation will also need to pull values along a row for column names that are >= & <= {Date.Year[Time UTC+10 + 3} , if that makes sense.

Cheers

Payeras_BI · ‎05-11-2022

Hi @bdenehy ,

See if this helps you.

let
  Origen = Table.FromRows(
    Json.Document(Binary.Decompress(Binary.FromText("dcu7DcAgDIThXVyfhF+AmQWx/xrgoEhpUn3F3T8naRS2osxBIB8wSRXd0oBo2iDjSAuTTL5FoLZbRP0r+BT+Fjkauh8rI+Semz7ntQE=", BinaryEncoding.Base64), Compression.Deflate)), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Date = _t, #"2008" = _t, #"2009" = _t, #"2010" = _t, #"2011" = _t, #"2012" = _t]
  ), 
  #"Tipo cambiado" = Table.TransformColumnTypes(Origen, {{"Date", type date}, {"2008", Currency.Type}, {"2009", Currency.Type}, {"2010", Currency.Type}, {"2011", Currency.Type}, {"2012", Currency.Type}}), 
  #"Índice agregado" = Table.AddIndexColumn(#"Tipo cambiado", "Index", 0, 1, Int64.Type), 
  #"Personalizada agregada" = Table.AddColumn(
    #"Índice agregado", 
    "4 years average", 
    each List.Average(
      {
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Índice agregado", Text.From(Date.Year([Date])))), 0, [Index] + 1)), 
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Índice agregado", Text.From(Date.Year([Date]) + 1))), 0, [Index] + 1)), 
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Índice agregado", Text.From(Date.Year([Date]) + 2))), 0, [Index] + 1)), 
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Índice agregado", Text.From(Date.Year([Date]) + 3))), 0, [Index] + 1))
      }
    ), 
    Currency.Type
  )
in
  #"Personalizada agregada"

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

v-yanjiang-msft · ‎05-24-2022

Hi @bdenehy ,

Has @Payeras_BI 's solution solved your problem? if so, would you mind accept his reply as solution? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

Payeras_BI · ‎05-20-2022

Hi @bdenehy ,

Find below a different approach, please let me know the result.
Just replace "Directory" with the path to your file to test it.

let
    Source = Csv.Document(File.Contents(Directory),[Delimiter=",", Columns=78, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Parsed Date" = Table.TransformColumns(#"Promoted Headers",{{"Time (UTC+10)", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Parsed Date", {"Time (UTC+10)"}, "Attribute", "Value"),
    QLD = Table.SelectRows(#"Unpivoted Columns", each Text.StartsWith([Attribute], "ASX Energy Contract QLD")),
    #"Renamed Columns" = Table.RenameColumns(QLD,{{"Value", "Price ($/MWh)"}, {"Time (UTC+10)", "Date"}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"Price ($/MWh)", Currency.Type}}, "es-US"),
    Year = Table.TransformColumns(#"Changed Type with Locale",{{"Attribute", each Text.Select(_,{"0".."9"}), type text}}),
    #"Sorted Rows" = Table.Sort(Year,{{"Date", Order.Ascending}, {"Attribute", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute]), "Attribute", "Price ($/MWh)", List.Sum),
    #"Filled Down" = Table.FillDown(#"Pivoted Column",Table.ColumnNames(#"Pivoted Column")),
    #"4 years average" = Table.AddColumn(#"Filled Down", "Custom", each List.Average(
      let
        year = Date.Year([Date])
      in
        {
          Record.Field(_, Text.From(year)), 
          Record.Field(_, Text.From(year + 1)), 
          Record.Field(_, Text.From(year + 2)), 
          Record.Field(_, Text.From(year + 3))
        }
    ),
    Currency.Type
    )
in
    #"4 years average"

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

Payeras_BI · ‎05-17-2022

@bdenehy ,

In case you still need transforming column types after pivoting.

let
    Source = Csv.Document(File.Contents("directory"),[Delimiter=",", Columns=78, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column78"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Time (UTC+10)", type datetimezone}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"Time (UTC+10)", type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Time (UTC+10)"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Price ($/MWh)"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Price ($/MWh)", Currency.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type3","ASX Energy Contract ","",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," Calendar Year Base Load Futures Price ($)","",Replacer.ReplaceText,{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each ([Attribute] = "QLD 2007" or [Attribute] = "QLD 2008" or [Attribute] = "QLD 2009" or [Attribute] = "QLD 2010" or [Attribute] = "QLD 2011" or [Attribute] = "QLD 2012" or [Attribute] = "QLD 2013" or [Attribute] = "QLD 2014" or [Attribute] = "QLD 2015" or [Attribute] = "QLD 2016" or [Attribute] = "QLD 2017" or [Attribute] = "QLD 2018" or [Attribute] = "QLD 2019" or [Attribute] = "QLD 2020" or [Attribute] = "QLD 2021" or [Attribute] = "QLD 2022" or [Attribute] = "QLD 2023" or [Attribute] = "QLD 2024" or [Attribute] = "QLD 2025")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Attribute", Order.Ascending}, {"Time (UTC+10)", Order.Ascending}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Sorted Rows","QLD","",Replacer.ReplaceText,{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value2", List.Distinct(#"Replaced Value2"[Attribute]), "Attribute", "Price ($/MWh)", List.Sum),
    #"Changed Type4" = Table.TransformColumnTypes(#"Pivoted Column", {{"Date", type date}, {"2008, Currency.Type"}, {"2009, Currency.Type"}, {"2010, Currency.Type"}, {"2011, Currency.Type"}, {"2012, Currency.Type"}, {"2013, Currency.Type"}, {"2014, Currency.Type"}, {"2015, Currency.Type"}, {"2016, Currency.Type"}, {"2017, Currency.Type"}, {"2018, Currency.Type"}, {"2019, Currency.Type"}, {"2020, Currency.Type"}, {"2021, Currency.Type"}, {"2022, Currency.Type"}, {"2023, Currency.Type"}, {"2024, Currency.Type"}, {"2025, Currency.Type"}, {"2026, Currency.Type"}, {"2027, Currency.Type"}, {"2028, Currency.Type"}, {"2029, Currency.Type"}, {"2030, Currency.Type"}, {"2031, Currency.Type"}, {"2032, Currency.Type"}, {"2033, Currency.Type"}, {"2034, Currency.Type"}, {"2035, Currency.Type"}}),
    #"Aggregate Index" = Table.AddIndexColumn(#"Changed Type4", "Index", 0, 1, Int64.Type), 
    #"Added Custom" = Table.AddColumn(
    #"Aggregate Index", 
    "4 years average", 
    each List.Average(
      {
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date])))), 0, [Index] + 1)), 
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date]) + 1))), 0, [Index] + 1)), 
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date]) + 2))), 0, [Index] + 1)), 
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date]) + 3))), 0, [Index] + 1))
      }
    ), 
    Currency.Type
  )
in
  #"Added Custom"

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

bdenehy · ‎05-11-2022

Thanks for your response @Payeras_BI .

I seem to encouter an EoF error at the line containing: #Changed Type" = Table.TransformColumnTypes(Source....

Here is my full console for the query:

let
    Source = Csv.Document(File.Contents("directory"),[Delimiter=",", Columns=78, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column78"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Time (UTC+10)", type datetimezone}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"Time (UTC+10)", type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Time (UTC+10)"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Price ($/MWh)"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Price ($/MWh)", Currency.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type3","ASX Energy Contract ","",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," Calendar Year Base Load Futures Price ($)","",Replacer.ReplaceText,{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each ([Attribute] = "QLD 2007" or [Attribute] = "QLD 2008" or [Attribute] = "QLD 2009" or [Attribute] = "QLD 2010" or [Attribute] = "QLD 2011" or [Attribute] = "QLD 2012" or [Attribute] = "QLD 2013" or [Attribute] = "QLD 2014" or [Attribute] = "QLD 2015" or [Attribute] = "QLD 2016" or [Attribute] = "QLD 2017" or [Attribute] = "QLD 2018" or [Attribute] = "QLD 2019" or [Attribute] = "QLD 2020" or [Attribute] = "QLD 2021" or [Attribute] = "QLD 2022" or [Attribute] = "QLD 2023" or [Attribute] = "QLD 2024" or [Attribute] = "QLD 2025")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Attribute", Order.Ascending}, {"Time (UTC+10)", Order.Ascending}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Sorted Rows","QLD","",Replacer.ReplaceText,{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value2", List.Distinct(#"Replaced Value2"[Attribute]), "Attribute", "Price ($/MWh)", List.Sum),
    
    _t = ((type nullable text) meta [Serialized.Text = True])
    in
        type table [Date = _t, #"2008" = _t, #"2009" = _t, #"2010" = _t, #"2011", #"2012" = _t, #"2013" = _t, #"2014" = _t, #"2015" = _t, #"2016" = _t, #"2017" = _t, #"2018" = _t, #"2019" = _t, #"2020" = _t, #"2021" = _t, #"2022" = _t, #"2023" = _t, #"2024" = _t, #"2025" = _t, #"2026" = _t, #"2027" = _t, #"2028" = _t, #"2029" = _t, #"2030" = _t, #"2031" = _t, #"2032" = _t, #"2033" = _t, #"2035" = _t]
    
        #"Changed Type" = Table.TransformColumnTypes(Source, {{"Date", type date}, {"2008, Currency.Type"}, {"2009, Currency.Type"}, {"2010, Currency.Type"}, {"2011, Currency.Type"}, {"2012, Currency.Type"}, {"2013, Currency.Type"}, {"2014, Currency.Type"}, {"2015, Currency.Type"}, {"2016, Currency.Type"}, {"2017, Currency.Type"}, {"2018, Currency.Type"}, {"2019, Currency.Type"}, {"2020, Currency.Type"}, {"2021, Currency.Type"}, {"2022, Currency.Type"}, {"2023, Currency.Type"}, {"2024, Currency.Type"}, {"2025, Currency.Type"}, {"2026, Currency.Type"}, {"2027, Currency.Type"}, {"2028, Currency.Type"}, {"2029, Currency.Type"}, {"2030, Currency.Type"}, {"2031, Currency.Type"}, {"2032, Currency.Type"}, {"2033, Currency.Type"}, {"2034, Currency.Type"}, {"2035, Currency.Type"}}),
        #"Aggregate Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), 
        #"Added Custom" = Table.AddColumn(
            #"Aggregate Index",
            "4 years average",
            each List.Average(
                {
                    List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date])))), 0, [Index] + 1)), 
                    List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date]) + 1))), 0, [Index] + 1)), 
                    List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date]) + 2))), 0, [Index] + 1)), 
                    List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date]) + 3))), 0, [Index] + 1))
                }
            ), 
            Currency.Type
        )
#"Added Custom"

Appreciate the help.

Cheers

Payeras_BI · ‎05-12-2022

Hi again @bdenehy ,

It seems when you tried to merge my code into yours you brought some extra redundant lines.

Please try the following (my code starts at #"Aggregate Index"):

let
    Source = Csv.Document(File.Contents("directory"),[Delimiter=",", Columns=78, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column78"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Time (UTC+10)", type datetimezone}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"Time (UTC+10)", type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Time (UTC+10)"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Price ($/MWh)"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Price ($/MWh)", Currency.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type3","ASX Energy Contract ","",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," Calendar Year Base Load Futures Price ($)","",Replacer.ReplaceText,{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each ([Attribute] = "QLD 2007" or [Attribute] = "QLD 2008" or [Attribute] = "QLD 2009" or [Attribute] = "QLD 2010" or [Attribute] = "QLD 2011" or [Attribute] = "QLD 2012" or [Attribute] = "QLD 2013" or [Attribute] = "QLD 2014" or [Attribute] = "QLD 2015" or [Attribute] = "QLD 2016" or [Attribute] = "QLD 2017" or [Attribute] = "QLD 2018" or [Attribute] = "QLD 2019" or [Attribute] = "QLD 2020" or [Attribute] = "QLD 2021" or [Attribute] = "QLD 2022" or [Attribute] = "QLD 2023" or [Attribute] = "QLD 2024" or [Attribute] = "QLD 2025")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Attribute", Order.Ascending}, {"Time (UTC+10)", Order.Ascending}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Sorted Rows","QLD","",Replacer.ReplaceText,{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value2", List.Distinct(#"Replaced Value2"[Attribute]), "Attribute", "Price ($/MWh)", List.Sum),
    #"Changed Type4" = Table.TransformColumnTypes(Source, {{"Date", type date}, {"2008, Currency.Type"}, {"2009, Currency.Type"}, {"2010, Currency.Type"}, {"2011, Currency.Type"}, {"2012, Currency.Type"}, {"2013, Currency.Type"}, {"2014, Currency.Type"}, {"2015, Currency.Type"}, {"2016, Currency.Type"}, {"2017, Currency.Type"}, {"2018, Currency.Type"}, {"2019, Currency.Type"}, {"2020, Currency.Type"}, {"2021, Currency.Type"}, {"2022, Currency.Type"}, {"2023, Currency.Type"}, {"2024, Currency.Type"}, {"2025, Currency.Type"}, {"2026, Currency.Type"}, {"2027, Currency.Type"}, {"2028, Currency.Type"}, {"2029, Currency.Type"}, {"2030, Currency.Type"}, {"2031, Currency.Type"}, {"2032, Currency.Type"}, {"2033, Currency.Type"}, {"2034, Currency.Type"}, {"2035, Currency.Type"}}),
    #"Aggregate Index" = Table.AddIndexColumn(#"Changed Type4", "Index", 0, 1, Int64.Type), 
    #"Added Custom" = Table.AddColumn(
    #"Aggregate Index", 
    "4 years average", 
    each List.Average(
      {
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date])))), 0, [Index] + 1)), 
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date]) + 1))), 0, [Index] + 1)), 
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date]) + 2))), 0, [Index] + 1)), 
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date]) + 3))), 0, [Index] + 1))
      }
    ), 
    Currency.Type
  )
in
  #"Added Custom"

If this didn't work consider sharing a sample of your csv file.

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

bdenehy · ‎05-16-2022

Thanks for your response Payeras.

What's tripping me up is the following line

 #"Changed Type4" = Table.TransformColumnTypes(Source,

PowerBI can't find the columns (e.g. Data, 2007, 2008...) because the source .csv is not in this format. PowerBI has manipulated the source .csv into this format for me.

How can I substitute source with something else, so that it works with the columns in this query (Date, 2007, 2008...)?

Cheers

Ben

v-yanjiang-msft · ‎05-16-2022

Hi @bdenehy ,

According to your description, modify it like this:

let
    Source = Csv.Document(File.Contents("directory"),[Delimiter=",", Columns=78, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column78"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Time (UTC+10)", type datetimezone}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"Time (UTC+10)", type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Time (UTC+10)"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Price ($/MWh)"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Price ($/MWh)", Currency.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type3","ASX Energy Contract ","",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," Calendar Year Base Load Futures Price ($)","",Replacer.ReplaceText,{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each ([Attribute] = "QLD 2007" or [Attribute] = "QLD 2008" or [Attribute] = "QLD 2009" or [Attribute] = "QLD 2010" or [Attribute] = "QLD 2011" or [Attribute] = "QLD 2012" or [Attribute] = "QLD 2013" or [Attribute] = "QLD 2014" or [Attribute] = "QLD 2015" or [Attribute] = "QLD 2016" or [Attribute] = "QLD 2017" or [Attribute] = "QLD 2018" or [Attribute] = "QLD 2019" or [Attribute] = "QLD 2020" or [Attribute] = "QLD 2021" or [Attribute] = "QLD 2022" or [Attribute] = "QLD 2023" or [Attribute] = "QLD 2024" or [Attribute] = "QLD 2025")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Attribute", Order.Ascending}, {"Time (UTC+10)", Order.Ascending}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Sorted Rows","QLD","",Replacer.ReplaceText,{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value2", List.Distinct(#"Replaced Value2"[Attribute]), "Attribute", "Price ($/MWh)", List.Sum),
    #"Aggregate Index" = Table.AddIndexColumn(#"Pivoted Column", "Index", 0, 1, Int64.Type), 
    #"Added Custom" = Table.AddColumn(
    #"Aggregate Index", 
    "4 years average", 
    each List.Average(
      {
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date])))), 0, [Index] + 1)), 
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date]) + 1))), 0, [Index] + 1)), 
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date]) + 2))), 0, [Index] + 1)), 
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date]) + 3))), 0, [Index] + 1))
      }
    ), 
    Currency.Type
  )
in
  #"Added Custom"

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

bdenehy · ‎05-17-2022

Thanks @v-yanjiang-msft this seems to work.
Issue now is the load is 100's of MB, and hasn't stopped.

Have you experienced this before with other calculations?

Cheers,
Ben

v-yanjiang-msft · ‎05-17-2022

Hi @bdenehy ,

Do you mean it loads slowly because the data model is too big.

Best Regards,
Community Support Team _ kalyj

bdenehy · ‎05-17-2022

Hi @v-yanjiang-msft

The data source is only 600kB. I'm unsure why introducing a new 'average' column would increase the size of the database >100x?

Cheers

Ben

v-yanjiang-msft · ‎05-17-2022

Hi @bdenehy ,

Complex operations do use a lot of CPU and memory, maybe that's causing it to load slowly.

Best Regards,
Community Support Team _ kalyj

Payeras_BI · ‎05-11-2022

Hi @bdenehy ,

See if this helps you.

let
  Origen = Table.FromRows(
    Json.Document(Binary.Decompress(Binary.FromText("dcu7DcAgDIThXVyfhF+AmQWx/xrgoEhpUn3F3T8naRS2osxBIB8wSRXd0oBo2iDjSAuTTL5FoLZbRP0r+BT+Fjkauh8rI+Semz7ntQE=", BinaryEncoding.Base64), Compression.Deflate)), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Date = _t, #"2008" = _t, #"2009" = _t, #"2010" = _t, #"2011" = _t, #"2012" = _t]
  ), 
  #"Tipo cambiado" = Table.TransformColumnTypes(Origen, {{"Date", type date}, {"2008", Currency.Type}, {"2009", Currency.Type}, {"2010", Currency.Type}, {"2011", Currency.Type}, {"2012", Currency.Type}}), 
  #"Índice agregado" = Table.AddIndexColumn(#"Tipo cambiado", "Index", 0, 1, Int64.Type), 
  #"Personalizada agregada" = Table.AddColumn(
    #"Índice agregado", 
    "4 years average", 
    each List.Average(
      {
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Índice agregado", Text.From(Date.Year([Date])))), 0, [Index] + 1)), 
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Índice agregado", Text.From(Date.Year([Date]) + 1))), 0, [Index] + 1)), 
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Índice agregado", Text.From(Date.Year([Date]) + 2))), 0, [Index] + 1)), 
        List.Last(List.Range(List.RemoveNulls(Table.Column(#"Índice agregado", Text.From(Date.Year([Date]) + 3))), 0, [Index] + 1))
      }
    ), 
    Currency.Type
  )
in
  #"Personalizada agregada"

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

Average based on column name and a date reference

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024