Formula to calculate difference between rows doesn't work properly for every row

Hi Anonymous ,

With the sample above and the following DAX code, I get the subsequent solution, which seems to be correct:

The code:

DateDiffColumnWithIndex = 
DATEDIFF (
    CALCULATE (
        MAX ( 'Table'[Total Days] ),
        FILTER ( 'Table',  'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    ),
   'Table'[Total Days],
    DAY
)   

The memory problem is of course a different kind of issue. I recommend to outsource the solution to Power Query instead. While DAX can do such things, it is way better to utilize PQ for this.

Result in PQ:

1) Add a new custom column with the following name and code:

2) Click on the left top corner in your query and choose merge query:

3) Do a left outer join on the same table again and choose the grouping and index columns for the upper one and the grouping and IndexNextRow columns for the lower part. Note, click on the columns in the right order and hold CTRL to select multiple columns:

4) click on the two arrows in the top right corner of the new column and just select change time:

5) Add a new column by calculating the duration in days:

6) remove all the columns that you do not need. 

Here the whole code that you could use in the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZdNi9swEIb/ivF5BZqR5A/dCr0s7WFh29OSQyBpMGydJcku9N9XM5KdOOuR3UI+TGI9Gb3vfCgvL2WjK2vLh/L5+zdl6ta4cG200rVCKLR3QN9dtpf3c7j4cSy+HsN7EZ70hS43DwsIaL1pJ4xTtz3sd7z6ocQsBSulG6JUHswt5Gnf77r+UDy/v70dTxe6taZfDU8VrlbwzIT32BdPp+PhtD+fBxatVSCzAki3vEPjrcsHZ2mrjoMzcnA6Aa3HZo732H/sz5fusL10x56AJF7FYVqZikOY1gPkw3S0mPauWpEHToFmnod2KuGv4+k3x0ZafnQ7NrkmXsNbd/8OHRKOKS1vtZEpdaI0HoWUq+lz0AySnYB2BIErxTRpSE7WVMEaFuJ8UJETzRRttAqAMO1dSX52EYDEArOUbi4R3R1RMBKAXISlshCxg5WJw9UAmdKvEwcab9y8cIDkJFScG6IBqMIj1unUgRnpUh5lm9IIRPiUHvPKxWW8bZlqrmG6elY4U3E+5YMbMaGLSLIxCGP7zZUkxlbpqiXVaGsIeV498rRdIxr3TDYsA20TtBH6BlgGmLxmIuUqWeTYrIWhgQ9b5BYzcn72u/2p+NJvX/+cuzPjDJdKHgeDlZW3E96MBbwaq1j44sgySoeawgK0h4mpkgmciZivB5k6uhAxTdZL7QZMKIDJdm9scOQPN3d54oVxl0Dg7cKpASq6wej8BkWgJFuk5k9IMnWULWLyR6QM5ipbBNGLk/MtYIKVgRQORwsjBnkMmHw5aEy80CftmqMCcjeNa/+DOsiWMNVKDHot9MkEotyVBzMdAxm0PJiRWyQ3y8zZY3ABQzXVa1TjrmQWxoJIHVVjjF0YCiZhIKgm1ChyF7J8psmM95EEHhfmC7KX3IHlIyQ219BWFSlyiDY/GmTqqFvE2JUYezdNb3SLILo986fDqvAIoNqjXVDNUqLZKl+jLvFC73bC8RS5zCxlTVFuNn8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Issue ID" = _t, #"Issue Key" = _t, #"Change Time" = _t, #"Field Name" = _t, #"Field Value" = _t, #"Total Days" = _t, index = _t, #"Days in Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Issue ID", Int64.Type}, {"Issue Key", type text}, {"Change Time", type datetime}, {"Field Name", type text}, {"Field Value", type text}, {"Total Days", Int64.Type}, {"index", Int64.Type}, {"Days in Status", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "IndexNextRow", each [index] + 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Issue ID", "index"}, #"Added Custom", {"Issue ID", "IndexNextRow"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Change Time"}, {"Changed Type.Change Time"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Changed Type", "Custom", each Duration.Days( Date.From([Change Time]) - Date.From([Changed Type.Change Time])))
in
    #"Added Custom1"

Does this help? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/