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

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 II
Super User II

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 II
Super User II

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 II
Super User II

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 II
Super User II

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 II
Super User II

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors