New, stopped, active and returned products

Introduction

I want to develop a report for the evolution of products. The question is whether a product that is sold is a new product in a given period (innovative product), a product that is not sold anymore in the given period (stopped product), is actively bought in a given period and a product that is sold in the previous period and is also sold in the current period.

Now, yet another interesting thing with this approach is that you can also use this in customer behavior analysis for customer retention, -attrition, -churning and -loyality.

Now there is something to say about this approach because it's not based on a certain definition and I accept this for this particular situation. The customer is happy with a year to year comparison between products. So, in this lab situation the logic is dependent on the filter context (in this case the calendar year slicer).

Lab 

I have made a small workbook with some sample data to experiment with the DAX expression. This is the data that is used for the building the expressions

And this is the model.

New products

Fist build your implicit measure SumNetrevenue and the Previous year calulation. A new product is a product that is sold in the current period (year) and is not sold in the previous period. You could argue that, I know, but for me it is ok enough, for now.

SumNetRevenue:=SUM(Fact[Revenue])
       
NR-1Year:=CALCULATE([SumNetRevenue];
                     FILTER(ALL(Period);
                            Period[Year] = MAX(Period[Year]) - 1
                            )
                    )

#NewProducts:=COUNTROWS(FILTER(VALUES(Fact[ProductID]);[NR-1Year] = 0))

Stopped products

A stopped product is a product that is sold in the previous period but is not sold in the current period. This is the DAX expression:

       
#LostProducts:=COUNTROWS(FILTER(Product; ([#ActiveProducts] = 0 && [NR-1Year] <> 0)))       
 

Active products

An active product is a product that is solfd in in the current year (and could have been sold in previous years)

       
#ActiveProducts:=DISTINCTCOUNT(Fact[ProductID])

Returned products

A returned product is a product that is sold in the previous period and is also sold in the current period. This DAX expression is borrowed from the DAXPatterns of Alberto Ferrari and Marco Russo.

       
#ReturningProduct:=COUNTROWS (
    CALCULATETABLE (
        VALUES ( Fact[ProductID] );
        VALUES ( Fact[ProductID] );
        FILTER (
            ALL ( Period );
           Period[PeriodID] < MIN (Period[PeriodID] )
        )
    )
)
 

Conclusion

Interesting DAX expressions to use in customer- or product analysis.

Greetz,

Hennie