SSAS Tabular grand total wrong with hierarchy filter

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?

Edit:

After further digging, I found out the problem seems to happen to the tabular cube itself when using MDX to query. I got the below two MDXs from SQL Server Profiler when the filters are applied in the Excel Pivot table.

Filter to date (2024/06/05):

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Calendar].[Date].[All]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS  FROM [Model] WHERE ([Calendar].[Year - Month - Date].[Date].&[2024-06-05T00:00:00],[Measures].[Total Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

Filter to month (2024/06/01):

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Calendar].[Date].[All]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS  FROM [Model] WHERE ([Calendar].[Year - Month - Date].[Month].&[2024-06-01T00:00:00],[Measures].[Total Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

By running in SSMS, the result is shown below: All and 6/5/2024 have the same value:

After I clear the cache using XMLA below:

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
        <DatabaseID>HierarchyFilterTest</DatabaseID>
    </Object>
</ClearCache>

The result becomes correct. But the problem is I cannot clear the cache in Excel. Anyone have idea of how to do it in Excel? Or is there other way to fix it?

I found out the issues seems to be because I am using the same column as both the hierarchy and dimension. If I explicitly define another column for the hierarchy, like below. The problem goes away.

The DateHierarchy is defined as:

DateHierarchy := 'Calendar'[Date]