historical data table - find date and type when customer upgrade happened?

[alya1]

Hello,

I have a historical customer table like below from date the customer joined till now:

Customer ID	Customer Type	Date
111	Basic	1/1/2020
111	Basic	1/2/2020
111	Main	1/3/2020
222	Advanced	1/1/2020
222	Pro	1/2/2020
222	Pro	1/3/2020
333	Basic	1/3/2020

with many more customer ID's, rows, and other columns.

Is there a way to extract when (which date) and to what (which type) the Customer Type changed last (either from one type to another or from nothing to an exisiting type if they are new)?

Customer ID	Cutomer Type	Date
111	Main	1/3/2020
222	Pro	1/2/2020
333	Basic	1/3/2020

Not sure if it would be better to make the new data as a column because the next step would be to get the datediff between last type changed date to todays date. 

Thank you!

[Anonymous]

Hi @alya1 ,

Thanks for the reply from @sevenhills , please allow me to provide another insight: 

1. Create a calculated column to sort the Customer Type.

Column =
SWITCH(TRUE(),
'Table'[Customer Type ] = "Basic",1,
'Table'[Customer Type ] = "Main",2,
'Table'[Customer Type ] = "Advanced",3,
'Table'[Customer Type ] = "Pro",4)

2. Create the calculation table.

LastCustomerTypeChange = 
SUMMARIZE(
    FILTER(
        'Table',
        'Table'[Column] = CALCULATE(MAX('Table'[Column]), ALLEXCEPT('Table', 'Table'[Customer ID  ]))
    ),
    'Table'[Customer ID  ],
    "CustomerType", LASTNONBLANK('Table'[Customer Type   ], MAX('Table'[Date])),
    "LastChangeDate", MIN('Table'[Date]),
    "DateDiffToady",DATEDIFF(MIN('Table'[Date]),TODAY(),DAY)
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alya1]

Hello Clara, thank you for your reply! Sadly, the customer type is extensive with many more categories then example. Sometimes customers go from higher level (like Pro) back to lower level (like Basic). Is there a way to show the latest Customer Type by date instead of MAX? 

[Anonymous · Accepted solution]

Hi @alya1 ,

I added some data.

Create the calculation table.

Table2 = 
VAR _currentId =
    MAX ( 'Table'[Customer ID  ] )
VAR _currentDate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Customer ID  ] = _currentId )
    )
RETURN
    SELECTCOLUMNS (
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE (
                    'Table',
                    'Table'[Customer ID  ],
                    'Table'[Customer Type   ],
                    'Table'[Date]
                ),
                "Maxdate",
                    CALCULATE (
                        MAX ( 'Table'[Date] ),
                        FILTER (
                            ALLSELECTED ( 'Table' ),
                            'Table'[Customer ID  ] = EARLIER ( 'Table'[Customer ID  ] )
                        )
                    ),
                "Outcome",
                    CALCULATE (
                        MIN ( 'Table'[Date] ),
                        FILTER (
                            ALLSELECTED ( 'Table' ),
                            'Table'[Customer ID  ] = EARLIER ( 'Table'[Customer ID  ] )
                                && 'Table'[Customer Type   ] = EARLIER ( 'Table'[Customer Type   ] )
                        )
                    )
            ),
            [Date] = [Maxdate]
        ),
        [Customer ID  ],
        [Customer Type   ],
        [Outcome]
    )

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alya1]

Hi Clara, This is amazing! I learned a lot from your reply 😄 Thank you so much

[sevenhills]

If it is DAX table, you are looking for, do these:

Go the modeling tab, click "New Table" and do this DAX:

Summary table = 
FILTER (
    'Table',
    'Table'[Date]
        = CALCULATE (
            MAX ( 'Table'[Date] ),
            ALLEXCEPT ( 'Table', 'Table'[Customer ID  ] )
        )
)

and format the date column per your needs

IP:

OP:

[sevenhills]

It was not clear whether you want to add new column or measure to calculate the date.

Add this measure to your summary table:

Diff Date Today = DATEDIFF(max('Table'[Date]),TODAY(),DAY)

Calculated column: very less likely you need today date diff as column, but giving that syntax:

Summary table 2 = ADDCOLUMNS(
FILTER (
    'Table',
    'Table'[Date]
        = CALCULATE (
            MAX ( 'Table'[Date] ),
            ALLEXCEPT ( 'Table', 'Table'[Customer ID  ] )
        )
), "Diff Today", DATEDIFF('Table'[Date], TODAY(), DAY)
)

[alya1]

Hello, thank you for your reply. Please note I would like "the date the customers type changed last". So for ID 222, is it possible to pull 1/2/2020?

[sevenhills]

what is overall output you need?

Yes, we can get the second largest one!

[alya1]

Thank you! Is there a way to extract when (which date) and to what (which type) the Customer Type changed last (either from one type to another or from nothing to an exisiting type if they are new)? A new table format is good. 

[sevenhills]

You need to provide the data sample of your output, as it is not clear what you are expecting In the output. 

[alya1]

It is the second table in the original question: 

"Is there a way to extract when (which date) and to what (which type) the Customer Type changed last (either from one type to another or from nothing to an exisiting type if they are new)?

Customer ID	Cutomer Type	Date
111	Main	1/3/2020
222	Pro	1/2/2020
333	Basic	1/3/2020

[sevenhills]

Based on @Anonymous solution, Now, I understand what you are looking for!

I saw you already accepted the solution! Great!

Based on Clara answer, I did this, as I was not sure what you are looking!

Now that I know what you are looking for, I am providing the same in another DAX

Summary table 3 = 
var _r = FILTER (
            ADDCOLUMNS( 
                SUMMARIZE('Table', 'Table'[Customer ID  ], 'Table'[Customer Type   ], 'Table'[Date])
                , "Min Date by CT"
                , CALCULATE(MIN('Table'[Date]), FILTER('Table', 'Table'[Customer ID  ] = EARLIER('Table'[Customer ID  ] ) && 'Table'[Customer Type   ] = EARLIER('Table'[Customer Type   ]) ))

                , "Max Date by C"
                , CALCULATE( MAX('Table'[Date]), FILTER('Table',  'Table'[Customer ID  ] = EARLIER('Table'[Customer ID  ] )))

                , "Count Rows", COUNTROWS('Table')

            ) , 'Table'[Date] = [Max Date by C] )

var _r2 =
        SELECTCOLUMNS(
            ADDCOLUMNS( _r
                 , "Date Diff Today"
                 ,  DATEDIFF([Min Date by CT],TODAY(),DAY) -- I prefere this as measure
            )
        , "Customer ID", [Customer ID  ]
        , "Customer Type", [Customer Type   ]
        , "Date", [Min Date by CT]
        , "Days as of Today", [Date Diff Today] 
        ) 

RETURN _r2

Output:

I added few extra columns to help in debugging like count rows!

Thank you