cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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

USA |35 | Jan 3

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

4 REPLIES 4
Community Support

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.

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Post Prodigy

Try;

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

That gives me the same results as my first formula.

Frequent Visitor