@dhanopiyaankit - This is an interesting problem and I don’t have any good optimized solution for you at the moment in case you would like to do it natively in Quick Sight. However, since I loved your problem statement, wanted to share an potential crude implementation to you which I was playing around with.
See the Above Snapshot and Months to Server is the final metric I believe what you are looking for. Sharing some of the calculations below for your reference as well.
Jan Sales :
sumover(sumIf({ Stock}, Date = “01/01/2023”), [ ], POST_AGG_FILTER)
Jan Cumulative Sales :
runningSum(sum({ Sales Forecast}), [Date ASC])
Feb Cumulative Sales :
runningSum(sum(ifelse(Date < “02/01/2023”, 0, { Sales Forecast})), [Date ASC])
Mar Cumulative Sales :
runningSum(sum(ifelse(Date < “03/01/2023”, 0, { Sales Forecast})), [Date ASC])
Apr Cumulative Sales :
runningSum(sum(ifelse(Date < “04/01/2023”, 0, { Sales Forecast})), [Date ASC])
Note : Please see the pattern in the computation logic
Final Metric → Months to Serve :
ifelse(
max(Date) = “01/01/2023”, sumOver(ifelse({Jan Sales} > {Jan Cumulative Sales}, 1, 0), [ ], POST_AGG_FILTER),
max(Date) = “02/01/2023”, sumOver(ifelse({Jan Sales} > {Feb Cumulative Sales}, 1, 0), [ ], POST_AGG_FILTER),
max(Date) = “03/01/2023”, sumOver(ifelse({Jan Sales} > {Mar Cumulative Sales}, 1, 0), [ ], POST_AGG_FILTER),
max(Date) = “04/01/2023”, sumOver(ifelse({Jan Sales} > {Apr Cumulative Sales}, 1, 0), [ ], POST_AGG_FILTER),
0
) -
(max(extract(“MM”, Date)) - 1)
Hope this helps! Thank you!