I have a table that is currently formatted in this way:
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 | TotalCanada | 32 | Jan 1
USA | 31 | Jan 1
Canada |43| Jan 2Canada |32| Jan 3
USA |35 | Jan 3There needs to be a special consideration for the fact that on the 2nd we had no US sales.
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
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.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.