Matrix with flags

[Anonymous]

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 .

Another question, is it a good idea to relate the calendar table to any of the main tables ? because I want to filter many dates in the different tables using that date table.

Any idea is appreciated !

Thank you 🙂

[lbendlin]

A calendar table should always be present.  You can then decide if and how to wire it into your data model.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...