cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Substract the value of the first date and last date of each year

Hi @Nolock 

 

I have to take the first and last value of FPR of each year in the table and substract those values

 

For example for year 2019: 20707.28 (26/12/2019 last date of 2019)-2982.06 (31/03/2019 first date of 2019), and then for the next years that can be infinite ( 2020, 2021 2022, 2023 etc ....)

 

restar valores misma columna.PNG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Substract the value of the first date and last date of each year

Hi @OscarSuarez10,

I've developed a short PowerQuery query which extracts the first and last value for every year and then substracts them.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcvBCQAxCATAXnyHZTWa5GoJ6b+NE8HvwNwrSiiM+skQlTdSAtZiJUZ4yyyZxGrxkkA2GhOiwLEbVsHBadjy3g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [TIME = _t, FPR = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"TIME", type date}, {"FPR", Int64.Type}}),
    SortedTable = Table.Sort(ChangedType, {"TIME"}),
    MinYear = Date.Year(Table.First(SortedTable)[TIME]),
    MaxYear = Date.Year(Table.Last(SortedTable)[TIME]),
    ListOfYears = {MinYear..MaxYear},
    TableFromList = Table.FromColumns({ListOfYears}),
    ChangedTypeToType = Table.TransformColumnTypes(TableFromList,{{"Column1", Int64.Type}}),
    RenameColumn = Table.RenameColumns(ChangedTypeToType, {{"Column1", "Year"}}),
    MinOfYear = Table.AddColumn(RenameColumn, "MinOfYear", 
        (curRecord) => 
            List.First(
                Table.SelectRows(
                    SortedTable, 
                    each Date.Year([TIME]) = curRecord[Year]
                )[FPR]
            ),
        type number
    ),
    MaxOfYear = Table.AddColumn(MinOfYear, "MaxfYear", 
        (curRecord) => 
            List.Last(
                Table.SelectRows(
                    SortedTable, 
                    each Date.Year([TIME]) = curRecord[Year]
                )[FPR]
            ),
        type number
    ),
    Substract = Table.AddColumn(MaxOfYear, "Last of year - first of year", each [MaxfYear] - [MinOfYear], type number)
in
    Substract

Another possible solution would be to use Group By Year and then extract the first and last value of every group.

 

View solution in original post

Super User
Super User

Re: Substract the value of the first date and last date of each year

Hi @OscarSuarez10,

the (curRecord) => functionBody is an anonymous function which I've used instead of each. Check the following slide, it should explain it.

 

Capture.PNG

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Substract the value of the first date and last date of each year

Hi @OscarSuarez10,

I've developed a short PowerQuery query which extracts the first and last value for every year and then substracts them.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcvBCQAxCATAXnyHZTWa5GoJ6b+NE8HvwNwrSiiM+skQlTdSAtZiJUZ4yyyZxGrxkkA2GhOiwLEbVsHBadjy3g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [TIME = _t, FPR = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"TIME", type date}, {"FPR", Int64.Type}}),
    SortedTable = Table.Sort(ChangedType, {"TIME"}),
    MinYear = Date.Year(Table.First(SortedTable)[TIME]),
    MaxYear = Date.Year(Table.Last(SortedTable)[TIME]),
    ListOfYears = {MinYear..MaxYear},
    TableFromList = Table.FromColumns({ListOfYears}),
    ChangedTypeToType = Table.TransformColumnTypes(TableFromList,{{"Column1", Int64.Type}}),
    RenameColumn = Table.RenameColumns(ChangedTypeToType, {{"Column1", "Year"}}),
    MinOfYear = Table.AddColumn(RenameColumn, "MinOfYear", 
        (curRecord) => 
            List.First(
                Table.SelectRows(
                    SortedTable, 
                    each Date.Year([TIME]) = curRecord[Year]
                )[FPR]
            ),
        type number
    ),
    MaxOfYear = Table.AddColumn(MinOfYear, "MaxfYear", 
        (curRecord) => 
            List.Last(
                Table.SelectRows(
                    SortedTable, 
                    each Date.Year([TIME]) = curRecord[Year]
                )[FPR]
            ),
        type number
    ),
    Substract = Table.AddColumn(MaxOfYear, "Last of year - first of year", each [MaxfYear] - [MinOfYear], type number)
in
    Substract

Another possible solution would be to use Group By Year and then extract the first and last value of every group.

 

View solution in original post

Re: Substract the value of the first date and last date of each year

That helped me a lot, but I have to choose the max  FPR month value and min month FPR value of each year and then substract them...

Super User
Super User

Re: Substract the value of the first date and last date of each year

It seaches for the minimum and maximum TIME value of a year and then takes the corresponding FPR value. It is the behaviour from your example. Is it wrong?

Re: Substract the value of the first date and last date of each year

Okey, Thank You !!

Re: Substract the value of the first date and last date of each year

Sorry, I don´t understand, What is "curRecord"?

Super User
Super User

Re: Substract the value of the first date and last date of each year

Hi @OscarSuarez10,

the (curRecord) => functionBody is an anonymous function which I've used instead of each. Check the following slide, it should explain it.

 

Capture.PNG

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 311 members 3,305 guests
Please welcome our newest community members: