Incorrect SQL generation in Calendar Slicer (Formula Picker) when using Last with Date granularity
Hello PA Team,
I’m seeing incorrect SQL generation in the Calendar Slicer when applying function "Last" with Date granularity. The filter produces single-day matches per period instead of a full range, which severely distorts aggregated results.
Details
You can reproduce this issue using the "Training Models - Sales" dataset. Simply build a table grouped by month with sales amounts.
Save this table. Create a dashboard and add the table to it. Also add a slicer "Date", and in its settings set:
· Initial Selection Mechanism → Functional
· Function Type → Last
· Value → 3 Months
You will see data for the last 3 months, but the figures will be incorrect.
This can be easily verified by adding the "Date" dimension to the table rows.
In the dashboard, you will see that the slicer leaves only one date per month in the table.
I expected to see data for the last 3 months relative to the date specified in the filter.
I am aware that changing the granularity from Date to Month in the calendar settings makes the calculation work correctly. However, this is not very convenient, as it would require manually adjusting the granularity setting each time.
Could you please clarify whether this behavior of the Calendar Slicer is considered correct by design, or is it a bug? Also, are there any workarounds that would allow me to configure the slicer to behave as expected — i.e., selecting a full date range rather than individual dates?
3 replies
-
Hello
This is indeed the designed outcome.
The result of last X is based on 3 ingredients: amount, hops and granularity.
1 - Amount: How many periods to select
2- Hops: Each period should jump backwards by year/month etc
3 - Granularity: Each period should be of this timespan
So if you do last 3 months at the month level you'll get May-Jun-Jul.
But if you do last 3 months at the day level you'll get 21th of each month.
If you do last 3 years at the month level, you'll get Jul of 2026,2025 and 2024.
This allows for more flexibility instead of always linking the hops and the granularity together.-
, I wasn't familiar with this yet, because I hadn't encountered a requirement like this before. But it's really very helpful!
But I have one more specific question: When you use the Date Calendar interactively in a dashboard, it works great. Here are two examples:
Formula: Last 3 Months - Granularity: Dates - Jump To: Current (here: today = 2026-07-29)
Formula: Last 3 Months - Granularity: Months - Jump To: Current (here: this month = 2026-07
However, if I want to set the "Granularity: Months" scenario as the "Initial Selection" in the Slicer Settings' "Load Time Settings", then I have to select "Functional" as mechanism - but I don't see an option to select the granularity there! I can only select "Last 3 Months," but then I automatically/implicitly get the "Granularity: Dates" scenario!
If I would select "Saved Selection by Name" as inital selection mechanism the initial scenario will not automatically adapt over time, i.e. it will stick to 2026-07, even if July has passed.
How could I achieve a dynamic/functional initial value with "Granularity" option?
-
& , if I had read the initial post more carefully, I wouldn't have posted this twice. Sorry about that!
I'm specifically interested in the initial settings for dashboards/presentations—I'd like them to be dynamic, but at the monthly level of granularity.
If this is not possible via Slicer Settings: Could you post an example with Formulates or a link to a corresponding help or commuity page, please?
-