Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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.
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
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.