Obtain different betwen row above

[JoaoPvP]

Hi to all,

First of all, thank you for the great forum. Usually, I can find the questions here without needing to post.

I have the following data:

Unique tag                  Date                    Entry data date
UT1                           1/1/2020                          1/12/2019

UT1                           2/1/2020                          4/12/2019

UT1                           5/1/2020                         6/12/2019

UT1                           3/1/2020                         8/12/2019

UT2                           2/2/2020                        1/12/2019

UT2                           15/2/2020                       4/12/2019

UT2                           10/2/2020                        6/12/2019

I would like to find a way to add a new column that would have the difference between Date on that row and Date on the previous row. For example, first-line would be blank, because that is the first line with that Tag. 2nd line would be 2/2/2020 - 1/1/2020, i.e., 1,0.

The Rows are ordered chronologically, based on Entry date value.


Could someone kindly help me.

Thank you,
João

[v-eachen-msft]

Hi @JoaoPvP ,

You could create a custom function to get previous row. Here is the link for your reference:

https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-po...

let func =   
 (Table as table, optional Step as number, optional SelectedColumns, optional GroupByColumns, optional Suffix as text, optional Buffer as any) =>

let
// Steps to prepare the (optional) parameters for the nested function "fnFetchNextRow"
    Source = if Buffer = null then Table else Table.Buffer(Table),
    Step0 = if Step = null then -1 else Step,
    Step_ = if Step = null then 1 else Number.Abs(Step),
    Suffix = if Suffix = null then ".Prev" else Suffix,
    GroupByColumns = if GroupByColumns = null then null else GroupByColumns,
    ShiftFunction = if Step0 < 0 then Table.RemoveLastN else Table.RemoveFirstN,
    ColNames = List.Buffer(Table.ColumnNames(Source)),
    NewColNames = if SelectedColumns = null then ColNames else SelectedColumns,
    CountNewCols = List.Count(NewColNames),

// Core function that retrieves values from previous or next rows (depending on sign of parameter "Step")
    fnFetchNextRow = (Table_ as table, optional Step as number, optional SelectedColumns, optional Suffix as text, optional Buffer as any) =>
        let
            MergeTable = if SelectedColumns = null then Table_ else Table.SelectColumns(Table_, SelectedColumns),
            Shift = if Step0 > 0 then ShiftFunction(MergeTable, Step_) & #table(NewColNames, List.Repeat({List.Repeat({null}, CountNewCols)}, Step_))
                                else #table(NewColNames, List.Repeat({List.Repeat({null}, CountNewCols)}, Step_)) & ShiftFunction(MergeTable, Step_),
            Reassemble = Table.ToColumns(Table_) & Table.ToColumns(Shift), 
            Custom1 = Table.FromColumns( Reassemble,  Table.ColumnNames(Source) & List.Transform(NewColNames, each _&Suffix ) )
        in
            Custom1,

// optional grouping on certain columns
    #"Grouped Rows" = Table.Group(Source, GroupByColumns, {{"All", each _}}, GroupKind.Local),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fnFetchNextRow([All], Step0, SelectedColumns, Suffix, Buffer)),
    #"Removed Columns" = Table.Combine(Table.RemoveColumns(#"Added Custom", GroupByColumns & {"All"})[Custom]),

// case no grouping
    NoGroup = fnFetchNextRow(Source, Step0, SelectedColumns, Suffix, Buffer),

// select case grouping
    Result = if GroupByColumns = null then NoGroup else #"Removed Columns"
in
    Result ,
documentation = [
Documentation.Name =  " Table.ReferenceDifferentRow ",
Documentation.Description = " Adds columns to a <code>Table</code> with values from previous or next rows (according to the <code>Step</code>-index in the 2nd parameter) ",
Documentation.LongDescription = " Adds columns to a <code>Table</code> with values from previous or next rows (according to the <code>Step</code>-index in the 2nd parameter) ",
Documentation.Category = " Table ",
Documentation.Source = "  ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann (www.TheBIccountant.com ) ",
Documentation.Examples = {[Description =  "  ",
Code = " Table.ReferenceDifferentRow( #table( {""Product"", ""Value""}, List.Zip( { {""A"" ,""A"" ,""B"" ,""B"" ,""B""}, {""1"" ,""2"" ,""3"" ,""4"" ,""5""} } ) ) ) ",
Result = " #table( {""Product"", ""Value"", ""Product.Prev"", ""Value.Prev""}, List.Zip( { {""A"" ,""A"" ,""B"" ,""B"" ,""B""}, {""1"" ,""2"" ,""3"" ,""4"" ,""5""}, {null ,""A"" ,""A"" ,""B"" ,""B""}, {null ,""1"" ,""2"" ,""3"" ,""4""} } ) ) "]}]
  
 in  
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

And you could get the result in DAX easily with EARLIER() function.

[mahoneypat]

Please see this M code for an example.  The example data didn't copy well so there are extra steps in the beginning to clean it up.  

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg0xVMANDPUN9Y0MjAzwqjACKjG0VIrViVYCE/iNNCJopAmpRpoSMtKMVBONCZlogc1EIzwmAlXjNRFrOOIz0NAUv4lYQxGvgQYEnIgUirEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique tag                  Date                    Entry data date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique tag                  Date                    Entry data date", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Unique tag                  Date                    Entry data date] <> "")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","                           "," ",Replacer.ReplaceText,{"Unique tag                  Date                    Entry data date"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","                          "," ",Replacer.ReplaceText,{"Unique tag                  Date                    Entry data date"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","                         "," ",Replacer.ReplaceText,{"Unique tag                  Date                    Entry data date"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","                        "," ",Replacer.ReplaceText,{"Unique tag                  Date                    Entry data date"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","                       "," ",Replacer.ReplaceText,{"Unique tag                  Date                    Entry data date"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value4", "Unique tag                  Date                    Entry data date", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Unique tag                  Date                    Entry data date.1", "Unique tag                  Date                    Entry data date.2", "Unique tag                  Date                    Entry data date.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Unique tag                  Date                    Entry data date.1", type text}, {"Unique tag                  Date                    Entry data date.2", type text}, {"Unique tag                  Date                    Entry data date.3", type date}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type1", {{"Unique tag                  Date                    Entry data date.2", type date}}, "en-150"),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type with Locale",{{"Unique tag                  Date                    Entry data date.1", "Unique Tag"}, {"Unique tag                  Date                    Entry data date.2", "Date"}, {"Unique tag                  Date                    Entry data date.3", "Entry data date"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Difference", each try Duration.Days([Date] - #"Added Index"[Date]{[Index] -1}) otherwise null)
in
    #"Added Custom"

To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with this M code. 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[JoaoPvP]

Hi @mahoneypat , thank you for getting back to me.

I believe I have made at least 2 mistakes when explaining this.

1-The date is coming from an excel file and looks like this:

Unique Tag	Date	Entry data date
UT1	1/1/2020	1/12/2019
UT1	2/1/2020	4/12/2019
UT1	5/1/2020	6/12/2019
UT1	3/1/2020	8/12/2019
UT2	2/2/2020	1/12/2019
UT2	15/2/2020	4/12/2019
UT2	10/2/2020	6/12/2019

2- I've oversimplified the problem and I'm not sure your code is taking this into account. Sometimes we have only 1 entry and other times we have more than 20.

I kindly ask you to check if the initial steps are still required and could you please teach me what your coding is doing?

Thank you very much,

João

[JoaoPvP]

Dunno why, but the table I've inserted lost the formating. It is a simple table with 3 columns.

[mahoneypat]

I looked at your initial post and see I misread it.  The 2/2 date comes from the next Unique Tag.  Please see this M code instead.  Still not 100% clear on your request.  This compares the earliest Date row for each Unique Tag.  I also got rid of the extra code for data clean up, so this starts with your example data.

If this is not what you are looking for, please share a table with the full expected output from this example dataset.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg0xVNJRMtQ31DcyMDKAMI2AbENLpVgdhLQRTNoEq7QpTNoMq7QxTNoCTdoIKGaEMBzdboi0oSkuy6HyBlhsjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique Tag" = _t, Date = _t, #"Entry data date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique Tag", type text}, {"Date", type date}, {"Entry data date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Unique Tag"}, {{"AllRows", each _, type table [Unique Tag=text, Date=date, Entry data date=date]}}),
FirstRow = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.First(_)}}),
#"Expanded AllRows" = Table.ExpandRecordColumn(FirstRow, "AllRows", {"Date", "Entry data date"}, {"Date", "Entry data date"}),
#"Added Index" = Table.AddIndexColumn(#"Expanded AllRows", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Days From Previous Date", each try Duration.Days([Date]-#"Expanded AllRows"[Date]{[Index]-1}) otherwise null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Days from Previous Data Entry Date", each try Duration.Days([Entry data date]-#"Expanded AllRows"[Entry data date]{[Index]-1}) otherwise null),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Unique Tag", "Date", "Entry data date", "Days From Previous Date", "Days from Previous Data Entry Date"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Unique Tag", type text}, {"Date", type date}, {"Entry data date", type date}, {"Days From Previous Date", Int64.Type}, {"Days from Previous Data Entry Date", Int64.Type}})
in
#"Changed Type1"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[JoaoPvP]

Hi @mahoneypat ,

Thank you for your time.

I cannot put your code to run. I'm creating a blank query and putting your code, but it just converts to text.

Can we find a way to do this in DAX? 

I was thinking, maybe we could create an index for each unique tag (like a count how many times this has appeared until now) Since the data is in chronological order, then I would just filter the information on index x to get the data from index x-1.

What do you think?

@v-eachen-msft , how would the earlier work?

Thank you.

[JoaoPvP · Accepted solution]

Got it!

So here is what I did:

1. Created a index that resets when the unique tag changes. 
   
   Changes =
   
   VAR CurIndex = Table[Index]
   VAR Curtag = Table[Unique Tag]

        calculate(countrows(table);filter(Table;Table[Unique Tag]=Curtag);filter(Table;Table[Index]<CurIndex))+1

     2. Searched for the previous Tag and computed the difference to the actual one:

VAR currentchange = Table[Changes]

VAR PreviousChange = Table[Changes]-1

VAR TAG = Table[Unique Tag]

RETURN

if(PreviousChange=0;format(0;"Fixed"); format(abs(

calculate(FIRSTNONBLANK(Table[date];TRUE());filter(TableP;Table[Unique Tag]=TAG);filter(table;Table[Changes]=PreviousChange))-calculate(FIRSTNONBLANK(Table[Date];TRUE());filter(Table;Table[Unique Tag]=TAG);filter(Table;Table[Changes]=currentchange)));"Fixed"))

 
Thank you all. Writing the problem and discussing with you made it easier to think.