periodToDateSumOverTime where fiscal year different to an actual year

I was using periodtodatesumover function so far to calculate the YTD sum on a monthly basis. The report was working fine but today I was given a task to duplicate the report for a company which has a fiscal year from march to march. And I do not know how to approach this. Any suggestions?

The problem is that there is no way to pick different granularities than those defined. periodToDateSumOverTime - Amazon QuickSight

periodToDateSumOverTime(sum({measure_forecast}), {snp_date}, PERIOD)

1 Like

To visualize, please find tables below

Here is okay
Service Year Period Actual Aggregated
2022 Jan 1 1
2022 Feb 2 3
2022 Mar 3 5
2022 Apr 4 7
2022 May 5 9
2022 Jun 6 11
2022 Jul 7 13
2022 Aug 8 15
2022 Sep 9 17
2022 Oct 10 19
2022 Nov 11 21
2022 Dec 12 23
And here is where problem appears
Service Year Period Actual Aggregated
2021 August 1
2021 September 2
2021 October 3
2021 November 4
2021 December 5
2022 January 6
2022 February 7
2022 March 8
2022 April 9
2022 May 10
2022 June 11
2022 July 12

I would subtract 3 months from your snp_date to make it look like march starts on January.

new_date = addDateTime(-3 , ‘MM’ , {snp_date})

Then use this new date in your previous calculate field.

periodToDateSumOverTime(sum({measure_forecast}), {new_date}, YEAR )

@Max ,
Any possibility to comparison last year ytd value with current year ytd value group by date category and also need % beteween both values with group by date category for each rows. if possible please help me out of this.