Forum Discussion

ade_kurniawan's avatar
ade_kurniawan
Frequent Visitor
4 years ago
Solved

Locate Previous Transaction Date & Place

Dear Friends,

 

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

here is the data :

ID CUSTOMERTRANSACTION DATELOCATION
AB1231/1/2022B
AB1231/7/2022A
AC3441/4/2022B
AC3441/5/2022B
AC3441/18/2022B
AC3441/29/2022A
AF7891/4/2022B
AF7891/17/2022A
AF7891/19/2022B

 

here is output that i want :

ID Customerprevious transaction dateLocation Previous transaction
AB1231/1/2022B
AC3441/18/2022B
AF7891/17/2022A

 

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

Link PBI File 

 

Thanks for help

DAX Command
DAX Commands and Tips

4 Replies

  • SpartaBI's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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