How can I make a formula that counts me in a 12 months rolling?


I have a dataset with the fields customer and date.
I can build a formula for distinct costumers and show for each month, but I’d like to make a formula to calculate the customers who have purchased at least once in the last 12 months, from the month I am showing,

I tried with runningsum but I can’t achieve it.


did you check


Yes, I’ve checked, but How can I set the 12 month rolling in this formula?

Maybe with an


But with that formula is only for the current month, but If I want to use distinct_count for March-23 where the range is from March-23 to Feb-22 doesn’t work. I tried to adapt with:

runningSum(distinct_count({customerId}),[truncDate(“DD”,{date}) ASC])

but there is an error message:

Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.

I’ll try to fix it changing the fields of the formula but it doesn’t work

What about

No, I think this function is not for moving date ranges

so actually you dont want a running sum rather then for each month the sum of the last 12 month?

Sorry if I explained myself wrong, what I mean is that I need to show by month the number of different users that there have been in that month and in the previous 11; in such a way that I start on November 23 measuring users from November 23 to December 22 and I end, for example, on January 23 measuring January 23 to February 22


first convert the date string into a Date:

Then use windows function to get the partition you want by date:

where 11 stands for the previous 11 months, and 1 stands for the row month. Change it as you deem fit.

This is the result:

I changed the field headers for simplicity and easy of use.

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!

Hi, thanks for your answer, with this solution counts all the customers but not the different or unique customers. Can you think of any other solution?