Monthly count of transactions by product over N last months with flags

[Atf23]

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 🙂

[talespin · Accepted solution]

hi @Atf23 

Created similar data model(Screenshot below) but with disconnected date table.

Please create this measure. When you place Product and Date from disconnected calendar table it will show error as there is no relationship between the two tables, that is fine, once you place this measure in table visual it will work.

Count Txn =

VAR _Product = SELECTEDVALUE(Products[Product])

VAR _Year = SELECTEDVALUE('CALENDAR'[YEAR])

VAR _Month = SELECTEDVALUE('CALENDAR'[MonthNo])

VAR _SelectedYearMonth = SELECTEDVALUE('CALENDAR'[YearMonth])

VAR _LaunchDate = CALCULATE( VALUES(Products[Launch_Date]), REMOVEFILTERS(), Products[Product] = _Product)

VAR _LaunchYearMonth = YEAR(_LaunchDate)&FORMAT( MONTH(_LaunchDate), "0#")

VAR _Sales = CALCULATE( SUM(Txn[No of sales]), REMOVEFILTERS(), Products[Product] = _Product, YEAR(Txn[Date]) = _Year && MONTH(Txn[Date]) = _Month && Txn[Date] >= _LaunchDate )

RETURN IF( _SelectedYearMonth >= _LaunchYearMonth, COALESCE(_Sales, 0), BLANK())

[Atf23]

Thanks, that works for me 🙂

[talespin]

hi @Atf23 

You're welcome.