DAX expression for finding the new row for a given customer

[sumsar10171]

Hi,

I'm trying to write a dax expression which will give me a new column for my table "meetings".

It's suppose to fetch the meeting type for the next meeting for the client.

Current table:

ID	CustomerID	Date	Type
1	1	2023-02-01	Startup
2	2	2023-02-01	Retention
3	3	2023-02-01	Startup
4	2	2023-02-05	Support
5	1	2023-02-05	Adjustment

What I'm looking for:

ID	CustomerID	Date	Type	Next meeting type
1	1	2023-02-01	Startup	Adjustment
2	2	2023-02-01	Retention	Support
3	3	2023-02-01	Startup
4	2	2023-02-05	Support
5	1	2023-02-05	Adjustment

[smpa01]

@sumsar10171  with new DAX OFFSET; PFA

Measure = 
CALCULATE (
    [__type],
    OFFSET (
        1,
        DISTINCT ( ALL ( tbl ) ),
        ORDERBY ( tbl[Date], ASC ),
        KEEP,
        PARTITIONBY ( tbl[CustomerID] )
    )
)

[sumsar10171]

Is it possible to write this as a column? 

[FreemanZ]

hi @sumsar10171 

try like:

Column = 
MINX(
    TOPN(
        1,
        FILTER(
            TableName,
            TableName[CustomerID] = EARLIER(TableName[CustomerID])            
                &&TableName[Date] > EARLIER(TableName[Date])      
        ),
        TableName[Date],
        DESC
    ),
    TableName[Type]
)

it worked like:

[sumsar10171]

It doesn't account for customers with more then two meetings.

In that case it will simply take the type of the latest meeting and apply to all previous meetings

[johnt75]

You can create a column like

Next meeting type = 
VAR NextMeeting = OFFSET(
	1,
	'Table',
	ORDERBY( 'Table'[Date], ASC),
	PARTITIONBY( 'Table'[CustomerID])
)
VAR MeetingType = SELECTCOLUMNS( NextMeeting, "Type", 'Table'[Type])
RETURN MeetingType 

If it complains that there may be duplicate rows you'll need to go into the modelling view, select the table and choose ID as the key column.