DAX measure to calculate consumers with upgraded product

[Anonymous]

Hi all, could you please advise me on how to create a measure to calculate number of consumers with upgraded product. Detail is as below.

In particular, "consumer with upgraded product" means his latest product has higher model versus the 1st one. As I set the slicer by purchase_date, I expect to get the same result as two tables in the bottom.

For example, up to Feb 10, as consumer A has not purchase model 3, we count this consumer in only the upgrade from model 1 to model 2. However, up to May 10, consumer A will be counted in the upgrade from model 1 to model 3, and he will be not counted in the upgrade from model 1 to model 2. 

Thanks.

[AlB]

@Anonymous 

That most likely has to do with the DISTINCT ( Table1[product_model] ) and would mean that there is more than one value for product_model for the earliest date  (in the filter context) of a specific Customer_id. OS if that is the case you'll need to decide how to treat that "anomaly" and update the code accordingly. The code above works fine with the sample data you provided. See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@Anonymous 

I meant the table you showed earlier rather than the original one.

1. Create two ancillary tables:

rowsT = ALL(Table1[product_model]) 

colsT = 
VAR min_ = MIN(Table1[product_model])
RETURN
FILTER(rowsT, rowsT[product_model] > min_ ) 

2. Create this measure:

Measure = 
VAR prodRow_ =
    SELECTEDVALUE ( rowsT[product_model] )
VAR prodCol_ =
    SELECTEDVALUE ( colsT[product_model] )
VAR auxT_ =
    ADDCOLUMNS (
        DISTINCT ( Table1[Id] ),
        "@first",
            VAR date_ =
                CALCULATE ( MIN ( Table1[purchase_date] ) )
            RETURN
                CALCULATE ( DISTINCT ( Table1[product_model] ), Table1[purchase_date] = date_ ),
        "@last",
            VAR date_ =
                CALCULATE ( MAX ( Table1[purchase_date] ) )
            RETURN
                CALCULATE (
                    DISTINCT ( Table1[product_model] ),
                    Table1[purchase_date] = date_,
                    Table1[product_upgrade] = "Yes"
                )
    )
RETURN
    COUNTROWS ( FILTER ( auxT_, [@first] = prodRow_ && [@last] = prodCol_ ) ) + 0

 3.  Create this measure, that uses the previous one:

Measure TOT = 
SUMX (
    CROSSJOIN (
        DISTINCT ( rowsT[product_model] ),
        DISTINCT ( colsT[product_model] )
    ),
    [Measure]
)

4. Place rowsT[product_model] in the rows of a matrix visual and colsT[product_model] in the columns. Place [Measure TOT] in Values of the visual

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi @AlB 

I have this issue when trying to put columns & measure in the visual. Did you face the same?

Thank you.

[AlB]

Hi @Anonymous 

Can you share the first table in text-tabular format, so that its contents can be copied?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi @AlB 

Yes, the original table is at this link. Data in product_upgrade changes a little bit with "Yes" for only purchase with upgraded product. Thank you for your help.