Monthly count of transactions by product over N last months with flags
Hi everyone, I have 3 datasets: Product, Client, Transactions and Calendar (Some Date - today()) as follows I need to compute the monthly count of transactions for each product starting over the last 15 months. The goal is to be able to flag products with no transactions over the last 15 months (red flag), products with no transactions over the last 12 months but there were some transactions over M-12, M-13, M-14 (orange flag) and products with at least one transaction over the last 12 months (green flag). Here is the sought-for output: product_id || product_name || M || M-1 || M-2 || ... ||M-12 || M-13 || M-14 || Total || Flag 1 Books 1 1 0 ... 0 0 0 2 green 2 Pens 0 0 0 ... 0 1 0 1 orange 3 Pencils 0 0 0 ... 0 0 0 0 red Total 1 1 0 ... 0 1 0 3 Here is the Model I succeeded (with the help of the community) to find the measure computing monthly total of transactions by product, the problem is that the matrix visual doesn't allow to get the output mentioned before where I can add a measure Flag to classifiy products Thank you 🙂
