How to calculate the moving total over a 14 day period when I have several groupings?

[20170120]

Hello,

I have a table that is currently formatted in this way:

Country	Product	Date	Store Centre	Payment Provider	Client	Transaction Type	Total	Project
USA	SKU100	1/20/2017	a	visa	ab2	repurchase	14	A
Canada	SKU100	1/15/2017	b	mastercard	ab1	initial	15	A
Italy	SKU110	12/5/2016	a	visa	ab4	repurchase	4	C

I also have a DimDate table.

I would like to know how I can get the moving sum of all transactions (over 14 days) in the last column that match the day in the table. Unfortunately my attempt has not been very successful:

14 Days:=CALCULATE(sum('All Transactions'[total]),
ALL('DimDate'),
FILTER(ALL('DimDate'),'DimDate'[Date]>=MAX('DimDate'[Date])-14&&'DimDate'[Date]<=max('DimDate'[Date])
) , VALUES('All Transactions'[Country])
)

What this returns is just the last 14 days' total values (not as a running total either. Any ideas on how to address? 

Thanks. 

Another thing: I believe an area where this is messing is when there is no directly previous day record for a certain country/client. I.e. 

Country | Total
Canada | 32 | Jan 1

USA | 31 | Jan 1

Canada |43| Jan 2
Canada |32| Jan 3

USA |35 | Jan 3

There needs to be a special consideration for the fact that on the 2nd we had no US sales. 

[v-qiuyu-msft]

Hi @20170120,

Please check if this article meet your requirement: Use Time intelligence functions to do a running sum of the last 6 months with PowerPivot.

If it doesn't meet your requirement, please share sample data and screenshots about the desired results.

Best Regards,
Qiuyun Yu

[ElliotP]

Try;

Running Total = Calculate(Sum(Sales), DATESINPERIOD(DateTable[DateKey], TODAY(), -14, DAY).

[20170120]

That gives me the same results as my first formula.

[20170120]

Added some more information to the OP.