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)
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.