Comparing Row Differences in a single dataset - DAX or Operation Help Needed!!

Hi Anonymous

First of all, I believe there's a bit of an inconsistency in the expected results you mention. I quote:

How many times did I have customer that changes 3 times, 2 times, 0 times, etc.

• 1 of my customers changed item purchased 3 times (Sum count of changes) 
• 1 of my customers changed item purchased 2 times (Sum count of changes) 
• 1 of my customers changed item purchased 0 times (Sume count of changes)

Why is the last one not 1 change? It should be, following the same reasoning as applied to the others. 

The code below considers that

- ID 123456 has 1 change (apple to orange)

- ID 789101 has  2 changes (Grapes to peaches, peaches to kiwis)

- ID 24424 has 0 changes (all bananas, so no change)

See the inconsistency i was referring to? If you want to consider that all bananas is 1 change, you can just remove the -1 at the end of the measure below.

So try this:

1. Place Table1[Customer ID] in the rows of a matrix visual

2. Create this measure and place it in values of the matrix

NumberOfChanges =
COUNTROWS (
    FILTER (
        Table1;
        VAR _PreviousYear =
            CALCULATE ( MAX ( Table1[Year] ); Table1[Year] < EARLIER ( Table1[Year] ) )
        VAR _PreviousItemPurchased =
            LOOKUPVALUE (
                Table1[Item Purchased];
                Table1[Customer ID]; Table1[Customer ID];
                Table1[Year]; _PreviousYear
            )
        RETURN
            Table1[Item Purchased]
            <> _PreviousItemPurchased
    )
)
    - 1

This will give you the number of changes per customer ID. From there you can do further calculations.

Code formatted with  

Hi,

Will this work?  You may dowload my PBI file from here.

Hope this helps.