I am trying to do a distinct year-to-date count in quicksight calculations. For example for 2022, if a user has used a product every month of the year, the user should only be counted once. I’m using the below formula to get this unique count
While this works, it does not partition by year. For Jan 2023, instead of starting a new unique YTD count, it carries over from December (included a screenshot of this)
Any ideas on how this formula can be modified to partition the calculation by year or anything else out there? TY
Also, when I add partition by year to the existing formula, it gives me only the new users for Jan 2023 so the number is understated due to the way the formula is. Happy to provide more details if needed.
Thank you for this, however, it does not quite work. When I add the year partition, it starts afresh for the whole year i.e. gives me only new IDs for Jan (28) which isn’t what I want.
The formula seems to be working as expected when I include a filter for the data, i.e. filter for year 2022 and then for year 2023, the results are correct… See Image - the first visual is with the partition added. 2022 is correct but 2023 isn’t.
The second visual filters for date in 2023 and these results are what I’m looking to get.
I want these in the same visual rather than having to split each year in a separate visual.
When I don’t use the year partition, the first visual carries over the whole year of 2022 (See highlighted in yellow in the second image) which is not what I need.
I can include the dataset behind these IDs and the dates and the outputs for the formula if this helps better! TY!