Forum Discussion

ThomasM's avatar
ThomasM
Frequent Visitor
9 years ago
Solved

Summarize Table

Hi All   I am trying to summarize a table that contains payment of customers.   I want the first transaction date and first amount, also the last transaction date and last amount.   As soon as ...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi ThomasM,

     

    Please add another two <groupBy_columnName> arguments in summarize function. Please try the following formula. I test it using your given sample data and get expected result.

     

    Table = SUMMARIZE(Table21,Table21[Rim_No],"FirstTransactionDate",MIN(Table21[Payment_Date]),"Total Amont Paid",SUM(Table21[Payment_Amont]),"LastTransactionDate",MAX(Table21[Payment_Date]),"first-amont",SUMX(FILTER(Table21,Table21[Payment_Date]=MIN(Table21[Payment_Date])),Table21[Payment_Amont]),"last-amont",SUMX(FILTER(Table21,Table21[Payment_Date]=MAX(Table21[Payment_Date])),Table21[Payment_Amont]))

     



    If you have any question, please feel free to ask.

    Best Regards,
    Angelia

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

I do not have your original data or a sample of your data, but why are you using SUMMARIZE? I would think that if you had a customer table and it was related to your transaction table that you could just create 4 columns in this table:

 

First Date = MIN(Table[Date])

Last Date = MAX(Table[Date])

First Amount = CALCULATE(SUM(Table[Amount]),FILTER(Table,Table[Date]=MIN(Table[Date])))

Last Amount = CALCULATE(SUM(Table[Amount]),FILTER(Table,Table[Date]=MAX(Table[Date])))

 

I may not have the DAX 100% correct, you may need to use RELATEDTABLE or RELATED, but that's the general gist of it. 

  • ThomasM's avatar
    ThomasM
    Frequent Visitor
    9 years ago

    Please see below table sample.

     

    The reason i created a summary table is because the original table contains multiple payments for each customer and therefore i only require a single line that contains id, first date, first amount and last date, last amount. Once i have this, i can figure out whether the customer is behind with payments or not, that means i am create more columns on the summary table.

     

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      Hi ThomasM,

       

      Please add another two <groupBy_columnName> arguments in summarize function. Please try the following formula. I test it using your given sample data and get expected result.

       

      Table = SUMMARIZE(Table21,Table21[Rim_No],"FirstTransactionDate",MIN(Table21[Payment_Date]),"Total Amont Paid",SUM(Table21[Payment_Amont]),"LastTransactionDate",MAX(Table21[Payment_Date]),"first-amont",SUMX(FILTER(Table21,Table21[Payment_Date]=MIN(Table21[Payment_Date])),Table21[Payment_Amont]),"last-amont",SUMX(FILTER(Table21,Table21[Payment_Date]=MAX(Table21[Payment_Date])),Table21[Payment_Amont]))

       



      If you have any question, please feel free to ask.

      Best Regards,
      Angelia

      • ThomasM's avatar
        ThomasM
        Frequent Visitor
        9 years ago

        Works perfectly. Thank you!!!