I am building a dashboard that includes reports for comparison of
- today and week to date (on a like-for-like basis), and
- month to date, quarter to date and year to date (same date basis)
between current and prior years, and forecast for the current year.
The fields/columns are date, sales amount, forecast sales amount and business unit (simplified version). Forecast sales amount field has data until end of the year (31 Dec 2026).
The requirements from the team are as follows:
- compare any date range using a filter or type-in value. For instance, today is 6 April 2026. But, they want to compare the sales performance until 2 April 2026.
- schedule a report to send in the morning via email of the performance until the previous day. So, this morning report should include the data until yesterday.
- Day -> 5 Apr 2026 (Sunday) vs. 6 Apr 2025 (Sunday) [like-for-like]
- WTD -> 30 Mar to 5 Apr 2026 vs. 31 Mar to 6 Apr 2025 [like-for-like]
- MTD -> 1 Apr to 5 Apr 2026 vs. 1 Apr to 5 Apr 2025 and so on for QTD and YTD. [same date]
I tried a few workarounds but they don't fulfill the requirements. I calculated prior year MTD, QTD and YTD. But let's say, once the date hits a new period, the calculation breaks since the report is until previous day. For instance for MTD, once it's 1 Apr, the report doesn't work anymore since there is no value for Mar. Or if you look at the week to date, once it gets to Monday morning, the WTD report doesn't work anymore (no value for previous week). And with the date filter as well, once it's applied, the calculations break.
I checked groupby shifting expressions, variables or calculations in Zoho Analytics, but can't find the solution for these requirements. I came from Tableau, so I know that there is a workaround in Tableau with the parameter. So, I'm hoping Zoho Analytics also might have something similar.
Thank you in advance.