Forum Discussion

alya1's avatar
alya1
Icon for Helper V rankHelper V
2 years ago
Solved

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

Hello,

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

Customer ID  Customer Type   Date
111 Basic1/1/2020
111Basic1/2/2020
111Main1/3/2020
222Advanced1/1/2020
222Pro1/2/2020
222Pro1/3/2020
333Basic1/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
111Main1/3/2020
222Pro1/2/2020
333Basic1/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!

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

12 Replies

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      sevenhills
      Icon for Super User rankSuper User
      2 years ago

      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's avatar
        alya1
        Icon for Helper V rankHelper V
        2 years ago

        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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      alya1
      Icon for Helper V rankHelper V
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.