SOLVED

Using a Date Parameter in a Calculation substraction latest year from 2010

Copper Contributor

I have a parameter that is a year.  Can it be passed to a calculated field?  The parameter will always grab the latest year and be subtracted from 2010.  So, if I get new data for 2020 the Parameter will grab the latest year and the calculation will use it for the subtraction (2019-2010).

3 Replies

@lmk001 

 

Absolutely it's possible. I don't see in your spreadsheet any spot where you're trying to do it, however. So it's hard to give anything specific. Let me just offer the following and invite you, if needed, to post a revised spreadsheet where you might include the formula where you're trying to make the calculation and not having it work.

First, though, let's see if a few pointers can help you make it work.

  1. Make sure that those numbers representing the years (the headings in some of your columns) are numbers and not text.  That appears to be the case.
  2. Then if you're using a full date (in Excel's "short date" format or some other), just enclose that in the YEAR function. For Example, =YEAR(TODAY()) will yield 2020, and you can subtract another field, 2012, from that to get 8.

As I said, though, I didn't see in your spreadsheet any attempt at a formula incorporating a parameter such as you describe.

@mathetes  I added the Parameter and the created the calculated column and I want the calculated column to be fed from the parameter

best response confirmed by lmk001 (Copper Contributor)
Solution

@lmk001 

It's not necessary to keep parameter as a table, for only one value named cell is more suitable

image.png

It is taken as

// LatestYear
let
    Source = Text.From(
        Excel.CurrentWorkbook(){[Name="LatestYear"]}[Content][Column1]{0}
    )
in
    Source

Resulting table

image.png

is generated by

// Population with LatestYear
let
    Source = Excel.CurrentWorkbook(){[Name="CO_1_10"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Locality"}}),

    // New part
    #"Duplicated Column which has parameter as name" = Table.DuplicateColumn(
        #"Renamed Columns",
        LatestYear, "Difference"
    ),
    #"Replace Value in it with difference to 2010" = Table.ReplaceValue(
        #"Duplicated Column which has parameter as name",
        each [Difference],
        each [Difference] - [2010],
        Replacer.ReplaceValue,{"Difference"}
    )
in
    #"Replace Value in it with difference to 2010"

hope it is self-explainable.

 

If you always take the difference from the last column of the table, you may avoid using parameter at all:

let
//Population without Parameter

    Source = Excel.CurrentWorkbook(){[Name="CO_1_10"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Locality"}}),

    // Take last column name
    columnNames = Table.ColumnNames(#"Renamed Columns"),
    lastColumnName = List.Last(columnNames),
    // calculate difference
        #"Duplicated Last Column" = Table.DuplicateColumn(
        #"Renamed Columns",
        lastColumnName, "Difference"
    ),
    #"Replace Value in it with difference to 2010" = Table.ReplaceValue(
        #"Duplicated Last Column",
        each [Difference],
        each [Difference] - [2010],
        Replacer.ReplaceValue,{"Difference"}
    )
in
    #"Replace Value in it with difference to 2010"

In general second column [2010] also could be taken from headers names:

let
//Population without hardcoding

    Source = Excel.CurrentWorkbook(){[Name="CO_1_10"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Locality"}}),

    // Take last column name
    columnNames = Table.ColumnNames(#"Renamed Columns"),
    firstYearColumnName = columnNames{1},
    lastColumnName = List.Last(columnNames),

    // calculate difference
        #"Duplicated Last Column" = Table.DuplicateColumn(
        #"Renamed Columns",
        lastColumnName, "Last"
    ),
        #"Duplicated First Column" = Table.DuplicateColumn(
        #"Duplicated Last Column",
        firstYearColumnName, "First"
    ),
    #"Add Difference" = Table.AddColumn(
        #"Duplicated First Column",
        "Difference",
        each [Last] - [First]
    ),
    #"We don't need First and Last any more" = Table.RemoveColumns(
        #"Add Difference",
        {"Last", "First"}
    )
in
    #"We don't need First and Last any more"
1 best response

Accepted Solutions
best response confirmed by lmk001 (Copper Contributor)
Solution

@lmk001 

It's not necessary to keep parameter as a table, for only one value named cell is more suitable

image.png

It is taken as

// LatestYear
let
    Source = Text.From(
        Excel.CurrentWorkbook(){[Name="LatestYear"]}[Content][Column1]{0}
    )
in
    Source

Resulting table

image.png

is generated by

// Population with LatestYear
let
    Source = Excel.CurrentWorkbook(){[Name="CO_1_10"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Locality"}}),

    // New part
    #"Duplicated Column which has parameter as name" = Table.DuplicateColumn(
        #"Renamed Columns",
        LatestYear, "Difference"
    ),
    #"Replace Value in it with difference to 2010" = Table.ReplaceValue(
        #"Duplicated Column which has parameter as name",
        each [Difference],
        each [Difference] - [2010],
        Replacer.ReplaceValue,{"Difference"}
    )
in
    #"Replace Value in it with difference to 2010"

hope it is self-explainable.

 

If you always take the difference from the last column of the table, you may avoid using parameter at all:

let
//Population without Parameter

    Source = Excel.CurrentWorkbook(){[Name="CO_1_10"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Locality"}}),

    // Take last column name
    columnNames = Table.ColumnNames(#"Renamed Columns"),
    lastColumnName = List.Last(columnNames),
    // calculate difference
        #"Duplicated Last Column" = Table.DuplicateColumn(
        #"Renamed Columns",
        lastColumnName, "Difference"
    ),
    #"Replace Value in it with difference to 2010" = Table.ReplaceValue(
        #"Duplicated Last Column",
        each [Difference],
        each [Difference] - [2010],
        Replacer.ReplaceValue,{"Difference"}
    )
in
    #"Replace Value in it with difference to 2010"

In general second column [2010] also could be taken from headers names:

let
//Population without hardcoding

    Source = Excel.CurrentWorkbook(){[Name="CO_1_10"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Locality"}}),

    // Take last column name
    columnNames = Table.ColumnNames(#"Renamed Columns"),
    firstYearColumnName = columnNames{1},
    lastColumnName = List.Last(columnNames),

    // calculate difference
        #"Duplicated Last Column" = Table.DuplicateColumn(
        #"Renamed Columns",
        lastColumnName, "Last"
    ),
        #"Duplicated First Column" = Table.DuplicateColumn(
        #"Duplicated Last Column",
        firstYearColumnName, "First"
    ),
    #"Add Difference" = Table.AddColumn(
        #"Duplicated First Column",
        "Difference",
        each [Last] - [First]
    ),
    #"We don't need First and Last any more" = Table.RemoveColumns(
        #"Add Difference",
        {"Last", "First"}
    )
in
    #"We don't need First and Last any more"

View solution in original post