topic Re: Row-wise Sum within a date range in DAX Commands and Tips

Row-wise Sum within a date range

CliffordAP — Mon, 03 May 2021 15:11:38 GMT

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

Re: Row-wise Sum within a date range

amitchandak — Mon, 03 May 2021 16:26:24 GMT

CliffordAP , Not very clear, Try a measure like

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

Re: Row-wise Sum within a date range

CliffordAP — Mon, 03 May 2021 16:33:47 GMT

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.