Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate Amount For Each Month

Hello Community ,

         I   have a table of  Invoice transaction  . I want to calculate the amount of transaction By MonthYear for each Client 

 

Source Data : 

       Client|Amount|Date 

       A           15         15/01/2020

       A           16         30/01/2020

       A           13         02/03/2020

       A            10        03/02/2020

 

Output Data : 

Client|Amount|Date 

       A           31      01/2020

       A           13      03/2020

       A            10     02/2020

 

Best Regards And Thanks for Your Help 

 

Need Help
Tips and Tricks
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, Anonymous 

     

    According to your description ,I think you can create a yearmonth column and a measure,then use it in table.

    Like this:

    YM = DATE(YEAR('Table'[Date]),MONTH('Table'[Date]),1)
    Measure = 
SUMX (
    SUMMARIZE (
        'Table',
        [Client],
        [YM],
        "a",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    [Client] = SELECTEDVALUE ( 'Table'[Client] )
                        && [YM] = SELECTEDVALUE ( 'Table'[YM] )
                ),
                [Amount]
            )
    ),
    [a]
)

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Either add a month year column to this table. Or better create a date tbale and have it there

    New column =  format([Date]), "mm/yyyy")

     

    Also, create a measure for the total amount

    Sum(Table[Amount])

     

    use two with client

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi ,

       Thanks for your suggestion my goal is to create a new table calculate the amount group by month for each customer 

       

      Best Regards 

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    According to your description ,I think you can create a yearmonth column and a measure,then use it in table.

    Like this:

    YM = DATE(YEAR('Table'[Date]),MONTH('Table'[Date]),1)
    Measure = 
SUMX (
    SUMMARIZE (
        'Table',
        [Client],
        [YM],
        "a",
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    [Client] = SELECTEDVALUE ( 'Table'[Client] )
                        && [YM] = SELECTEDVALUE ( 'Table'[YM] )
                ),
                [Amount]
            )
    ),
    [a]
)

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

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