Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
20170120
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:

 

CountryProductDateStore CentrePayment ProviderClientTransaction TypeTotalProject
USASKU1001/20/2017avisaab2repurchase14A
CanadaSKU1001/15/2017bmastercardab1initial15A
ItalySKU11012/5/2016avisaab4repurchase4C

 

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. 

4 REPLIES 4
v-qiuyu-msft
Community Support
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.

 

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

 

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.
ElliotP
Post Prodigy
Post Prodigy

Try;

 

 

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

That gives me the same results as my first formula.

Added some more information to the OP.

 

 

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors