Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Group data by consecutive dates per client

Hello,

 

This is the general structure of my data, where each row represents a single transaction currently:

 

ClientIDTransaction_DateAmount
aa01/01/2010
aa01/02/2015
aa01/05/2013
bb02/06/2011
bb02/02/204

 

What I would like to do is group transactions per client by consecutive dates, so client "aa" would show 2 transactions -- one for 25

and one for 13 ,because the first two transactions happen on consecutive dates, so they are counted as a single transaction, and client "bb" would show 2 transactions for 11 and 4. 

 

I don't need to preserve the date data, what I'm trying to show is average transaction amounts by client where transactions with consecutive dates count as a single transaction.

 

 

Any help would be really appreciated, thanks!

Need Help
  • dax's avatar
    dax
    6 years ago

    Hi Anonymous , 

    You could try below M code to see whether it woks or not

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxU0lEyMNQHIiMDINPQQClWB0nYCCpsiipsChU2BgsnJYGEjfQNzKDChqjCUENMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ClientID = _t, Transaction_Date = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", type text}, {"Transaction_Date", type date}, {"Amount", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Transaction_Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ClientID"}, {{"ALL", each _, type table [ClientID=text, Transaction_Date=date, Amount=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "GROUP INDEX", each Table.AddIndexColumn([ALL], "INDEX", 0,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ALL"}),
    #"Expanded GROUP INDEX" = Table.ExpandTableColumn(#"Removed Columns", "GROUP INDEX", {"Transaction_Date", "Amount", "INDEX"}, {"Transaction_Date", "Amount", "INDEX"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded GROUP INDEX", "Custom", each try Date.AddDays([Transaction_Date],1)=#"Expanded GROUP INDEX"[Transaction_Date]{[INDEX]+1} otherwise null),
    Custom1 = Table.ReplaceValue(#"Added Custom1", each [Custom], each if [Custom] = false or Value.Is([Custom], type date) then [Transaction_Date] else null , Replacer.ReplaceValue, {"Custom"}),
    #"Filled Up" = Table.FillUp(Custom1,{"Custom"}),
    #"Grouped Rows1" = Table.Group(#"Filled Up", {"ClientID", "Custom"}, {{"sum", each List.Sum([Amount]), type number}, {"all", each _, type table [ClientID=text, Transaction_Date=date, Amount=number, INDEX=number, Custom=date]}})
in
    #"Grouped Rows1"

     You could refer to extract-start-and-end-dates-with-power-query  for details.

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi Anonymous ,

     

    Try creating this calculated column:

     

    Consecutive =
    VAR _date = 'Table'[Transaction_Date]
    VAR _lastDate = CALCULATE(MAX('Table'[Transaction_Date]); FILTER(ALL('Table'); 'Table'[ClientID] = EARLIER('Table'[ClientID]) && 'Table'[Transaction_Date] < _date))
    VAR _nextDate = CALCULATE(MIN('Table'[Transaction_Date]); FILTER(ALL('Table'); 'Table'[ClientID] = EARLIER('Table'[ClientID]) && 'Table'[Transaction_Date] > _date))
    RETURN IF('Table'[Transaction_Date] - _lastDate = 1 || _nextDate - 'Table'[Transaction_Date] = 1; TRUE(); FALSE())
     

    If you consider it as a solution, please mark as a solution and kudos.

    Ricardo

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hey, 

       

      I'm pretty new to DAX, when I tried creating that column, and replacing the table and field names, it gives me a whole bunch of error lines at and after the semi colons. What am I missing?

       

       

      thanks,

       

       

      • camargos88's avatar
        camargos88
        Community Champion
        6 years ago

        Anonymous ,

         

        Try changing semicolon to coma.

         

        Ricardo

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi Anonymous , 

    You could try below M code to see whether it woks or not

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxU0lEyMNQHIiMDINPQQClWB0nYCCpsiipsChU2BgsnJYGEjfQNzKDChqjCUENMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ClientID = _t, Transaction_Date = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", type text}, {"Transaction_Date", type date}, {"Amount", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Transaction_Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ClientID"}, {{"ALL", each _, type table [ClientID=text, Transaction_Date=date, Amount=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "GROUP INDEX", each Table.AddIndexColumn([ALL], "INDEX", 0,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ALL"}),
    #"Expanded GROUP INDEX" = Table.ExpandTableColumn(#"Removed Columns", "GROUP INDEX", {"Transaction_Date", "Amount", "INDEX"}, {"Transaction_Date", "Amount", "INDEX"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded GROUP INDEX", "Custom", each try Date.AddDays([Transaction_Date],1)=#"Expanded GROUP INDEX"[Transaction_Date]{[INDEX]+1} otherwise null),
    Custom1 = Table.ReplaceValue(#"Added Custom1", each [Custom], each if [Custom] = false or Value.Is([Custom], type date) then [Transaction_Date] else null , Replacer.ReplaceValue, {"Custom"}),
    #"Filled Up" = Table.FillUp(Custom1,{"Custom"}),
    #"Grouped Rows1" = Table.Group(#"Filled Up", {"ClientID", "Custom"}, {{"sum", each List.Sum([Amount]), type number}, {"all", each _, type table [ClientID=text, Transaction_Date=date, Amount=number, INDEX=number, Custom=date]}})
in
    #"Grouped Rows1"

     You could refer to extract-start-and-end-dates-with-power-query  for details.

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.