12 month rolling pivot table with filtering capabilities

SBdomo21

Hi all, I have a customer that is looking for a way to create a pivot table where value for each month is a sum of current month + previous 11 months (so in total we have 12 rolling months). For example, value for Dec'22 = sum of values from Jan'22 to Dec'22.

Below is the formula that they created for this but when there are gaps they are finding that the formula won't work any longer. Does anyone have any suggestions of how to resolve this filtering dilema?

For columns we have date (Month+Year), for rows we have some other dimension (in this example it's competitor). For rows we show top 10 items, while remaining items should be aggregated to "other" category.We want to be able to filter the table by any of dimensions that's available in the data (there's a lot of dimensions).Note on the data: data is very granular, there are multiple dimensions and date fields are daily (day-month-year).So far we've got a card which kind works with use of window functions, but the issues with this setup is that 1) calculation doesn't work if there is gap for even one month (there are many gaps, especially when you add multiple filters for other dimensions) 2) we're not able to show top 10 + aggregate rest to other category.Let me know if you have any questions, I can walk you through all details.Dataset used: Sales Opportunity LineFormula used:sum(`ACV Usd`)
+
lead(sum(`ACV Usd`), 1) over (partition by `LOB` order by `Estimated Close Year Month` desc)
+
lead(sum(`ACV Usd`), 2) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 3) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 4) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 5) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 6) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 7) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 8) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 9) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 10) over (partition by `LOB` order by `Estimated Close Year Month` desc )
+
lead(sum(`ACV Usd`), 11) over (partition by `LOB` order by `Estimated Close Year Month` desc )