topic Re: Locate Previous Transaction Date & Place in DAX Commands and Tips

Locate Previous Transaction Date & Place

ade_kurniawan — Thu, 02 Jun 2022 07:36:15 GMT

Dear Friends,

Please help me, i want to locate previous transaction : date & location.

here is the data :

ID CUSTOMER	TRANSACTION DATE	LOCATION
AB123	1/1/2022	B
AB123	1/7/2022	A
AC344	1/4/2022	B
AC344	1/5/2022	B
AC344	1/18/2022	B
AC344	1/29/2022	A
AF789	1/4/2022	B
AF789	1/17/2022	A
AF789	1/19/2022	B

here is output that i want :

ID Customer	previous transaction date	Location Previous transaction
AB123	1/1/2022	B
AC344	1/18/2022	B
AF789	1/17/2022	A

I prefer to use DAX command, because i use live connection.

Link PBI File

Thanks for help

Re: Locate Previous Transaction Date & Place

SpartaBI — Thu, 02 Jun 2022 07:54:08 GMT

ade_kurniawan create these measures:

Previous transaction date = VAR _current_date = MAX(Sheet1[TRANSACTION DATE]) VAR _current_id = SELECTEDVALUE(Sheet1[ID CUSTOMER]) RETURN CALCULATE( MAX(Sheet1[TRANSACTION DATE]), REMOVEFILTERS(Sheet1), Sheet1[ID CUSTOMER] = _current_id, Sheet1[TRANSACTION DATE] < _current_date ) Location of Previous transaction date = VAR _current_date = MAX(Sheet1[TRANSACTION DATE]) VAR _current_id = SELECTEDVALUE(Sheet1[ID CUSTOMER]) VAR _previous_transaction_date = CALCULATE( MAX(Sheet1[TRANSACTION DATE]), REMOVEFILTERS(Sheet1), Sheet1[ID CUSTOMER] = _current_id, Sheet1[TRANSACTION DATE] < _current_date ) RETURN CALCULATE( SELECTEDVALUE(Sheet1[LOCATION]), REMOVEFILTERS(Sheet1), Sheet1[ID CUSTOMER] = _current_id, Sheet1[TRANSACTION DATE] = _previous_transaction_date )

Put them next to the ones you already have in the table.
You need to also put the current transaction data in the visual for it to work. Previous need to be previous to something:

Showcase Report – Contoso By SpartaBI

Re: Locate Previous Transaction Date & Place

CNENFRNL — Thu, 02 Jun 2022 08:37:50 GMT

Re: Locate Previous Transaction Date & Place

ade_kurniawan — Thu, 02 Jun 2022 11:16:05 GMT

Thanks for help SpartaBI & CNENFRNL but i have another problem :
my data is in different table & i cant use variable that hide in table : PBI separated table

thanks

Re: Locate Previous Transaction Date & Place

SpartaBI — Thu, 02 Jun 2022 19:18:38 GMT

ade_kurniawan hey, check this for my vesrion.
Is that what you need?
Transaction Customer v4.pbix

Showcase Report – Contoso By SpartaBI