Dynamic YTD Calculation depending on todays date for a count of Number of Events

[Tim_groot]

Hi,

I’ve got a dataset that includes the number of events that happen in a year. For a year on year comparison I would like to only include the “Year to Date” data for every year depending on today’s date.

e.g. if Today’s date is 21st of August 2023. I would only want to include events that are in the dataset for every year that are before 21st of August 2022, 21st of August 2021, 21st of August 2020 etc.

I’ve included examples in the screenshots below to illustrate what I’m looking for:

Any help would be much appreciated for how to achieve this in Quicksight!

[Max]

This article should give you your solution.

Creating period comparisons with QuickSight's periodOver and periodTo functions

This article uses the SaaS-Sales Dataset that you can download as part of the QuickSight Author Workshop. We will update the data to the current date so your calculations are realistic. If you do not have a QuickSight account please follow the instructions in the Initial Setup. Instructions to create the dataset can be found in Exercise 1 of the Build your first dashboard. Create an analysis for the dataset. The following steps show you how to create visuals to show data from the current year,…

[Tim_groot]

Hi Max,

Thank you, that’s super helpful!

I’ve made good progress on this as you can see in the screenshot below:

The main challenge I’m left with is that as you can see in the bar chart on the right is that I’m having to create an individual calculated field for each year. It would be preferred if I only had to create a single calculated field that would calculate the YTD for every year in the dataset.

I tried to look at “RunningSum” but not sure if that’s the right way to go here. Any help would be greatly appreciated!

Best
T

[Max]

Hey @Tim_groot,

Can you share the calculated field you are using right now?

I have a few ideas in mind that might help with your bar chart.

[Tim_groot]

Hi Max,

Thank you for offering to help.

I’ve created 3 calculated fields with the following calculations:

Events - Current YTD: periodToDateDistinctCount( {Event Name}, {Event Start Date}, ‘YEAR’, now())
Events - Previous YTD: periodToDateDistinctCount({Event ID}, {Event Start Date}, ‘YEAR’,addDateTime(-1, ‘YYYY’, now()))
Events - -2 YTD: periodToDateDistinctCount({Event ID}, {Event Start Date}, ‘YEAR’, addDateTime(-2, ‘YYYY’, now()))

The graph I’m able to make with the above calculated fields is for example:

As mentioned, I don’t want to make 3 individual calculated fields because this limits the number of graphs I can make to showcase the YoY trends, etc. It also means that if I wanted to show 3, 4 or 5 years of data I would have to make more calculated fields which isn’t scalable.

Any help would be greatly appreciated to be able to have one calculated field that automatically only takes YTD data for every year as opposed to having to make multiple calculated fields.

Thank you for your help!

Tim

[duncan]

Hello @Tim_groot !

You can accomplish this via the calc below:

Ifelse( 
periodToDateDistinctCount({Event Name}, {Event Start Date}, ‘YEAR’, now()), 
'Events - Current YTD', 
periodToDateDistinctCount({Event ID}, {Event Start Date}, ‘YEAR’,addDateTime(-1, ‘YYYY’, now())),
'Events - Previous YTD',
periodToDateDistinctCount({Event ID}, {Event Start Date}, ‘YEAR’, addDateTime(-2, ‘YYYY’, now())),
'Events - -2 YTD', 
NULL)

[Tim_groot]

Thank you Duncan! Really appreciate the help!