Dynamic Group By transactions within the past 1 year

[rgblaze21]

The objective is to determine whether a customer's total transaction balance has reached the maximum threshold for the past year (365) days every time the customer transacts.

For example, Customer A has a transaction on July 1, 2023 for a total of $100. I need to check Customer A's total transaction amount from July 1, 2022 to June 30, 2023 (or within 1 year prior to July 1, 2023). This checking would be done whenever he has a transaction.

Currently, I have a single dataset of historical orders for all customers for the past 2 years containing the following fields (Customer, Transaction Date, Amount). 

My idea is for every customer transaction record, I must have another column that will show the total balance of that customer for the past 365 days. Like this:

Customer	Transaction Date	Amount	Customer's Total Amount  (for the past 365 days prior to Transaction Date)
A	July 1, 2023	100	<Total Sum of Customer A's transactions filtered from July 1, 2022 to June 30, 2023>
B	July 3, 2023	150	<Total Sum of Customer B's transactions filtered from July 3, 2022 to July 2, 2023>

Any idea on how to go about it in Power Query? Thanks a lot!

[j_ocean]

"This checking would be done whenever he has a transaction."

Is PBI where you want to be doing this check? It sounds like something that should be further upstream before the transaction is authorized.

But anyway this can be done in Power Query by referencing the table, adding a column with todays date -365 days, a logic column to filter out older stuff, and a group-by. Link it by customer ID to the main table. Depending on how you do it you now have a way to display the last refresh date and a "transactions since..." date in your report.

[wdx223_Daniel]

Power Query is not good at this job to group data dynamicly.

suggest to do it with DAX

Measure=VAR _d=MAX(Table[Transaction Date]) RETURN CALCULATE(SUM(Table[Amount]),Table[Transaction Date]<_d&&DATEDIF(Table[Transaction Date],_d,YEAR)<1)