This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(105.60000000000001, 80%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EV%3C/text%3E%3C/svg%3E)
I am using SSAS Tabular 2022 and has model as below:
The total amount is calculated as
Total Amount:= sum(Amount[Amount])
When I view in Excel and filter the model by the Year-Month-Date, the first time under any level of the filter is always correct. For example, I filter first on date 2024-05-04:
But then when I filter other date in May or May itself (one level up), the grand total does not change and remains what I selected previously. For example, I filter on month 2024-05-01:
I tried refreshing data in data tab but the results are still the same. Why is that? And how should I fix it?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(316.8, 41%, 40%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EVR%3C/text%3E%3C/svg%3E)
Hi vincent,
I wanted to check if you had the opportunity to review the information provided and resolve the issue..? Please let us know if you need any further assistance. We are happy to help.
Thank you.
Hi vincent,
Thank you for reaching out to Q&A Forum.
The issue is not with your DAX formula but with Excel caching the old filter context when navigating SSAS hierarchies. When you move from a date-level (e.g., 2024/5/4) to a higher level (e.g., May 2024), Excel doesn’t always refresh the MDX context, so the total remains unchanged.
To fix it, redefine your measure as:
Total Amount =
CALCULATE(SUM(Amount[Amount]), KEEPFILTERS(VALUES(Calendar[Date])))
This ensures totals recalculate correctly based on the active date filter.
Alternatively, refresh the PivotTable or convert it to formulas to force recalculation.