Forum Discussion

CliffordAP's avatar
CliffordAP
Helper II
5 years ago

Row-wise Sum within a date range

Hello all,

I am trying to do an aggregation of transactions that happen between people and between a date range of 7 days.

I want this calculation to be done row-wise, meaning that every transaction will show a sum of transactions within the last 7 days.

Here is what the data looks like, and the final column is the output I am looking for.

See how for every record, the calculation sums all transactions within the last 7 days for that person.

Does anyone have any ideas? I have tried a few CALCULATE functions, and I don't see any posts similar to this. I'm sure there must be a way to do this.

 

Thank you

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    CliffordAP , Not very clear, Try a measure like

    calculate(sum(Table[Amount]), filter(Table, Table[Date] >=today() -7 && Table[Date] <=today()))

    • CliffordAP's avatar
      CliffordAP
      Helper II
      5 years ago

      Ah, I do need to clarify.
      I don't want the previous 7 days from today, I need the previous 7 days from the recorded date.
      So for each record, the calculation will look into the previous 7 days from the recorded date and sum the transactions within those 7 days.
      I'm getting something close with;
      CALCULATE(SUM('table'[amount])
                      ,ALLSELECTED()
                      ,DATESBETWEEN('table'[Date],'table'[Date],DATEADD('table'[Date],-7,DAY)))


      But it's not perfect..

      Thank you for your response.