Forum Discussion

RSSILVA_22's avatar
RSSILVA_22
Icon for Helper I rankHelper I
3 years ago
Solved

remaining column to return previous payment amount

Good afternoon. I need help creating a calculated column that returns the customer's previous payment amount.   example. Line 1 of the image below. For the customer abc123, the value of the first...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi RSSILVA_22 

    if you're looking for a calculated column then please try

    Previous Payment =
    VAR CurrentDate = 'Table'[payment date]
    VAR CurrentTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[cod_client] ) )
    VAR TableBefore =
    FILTER ( CurrentTable, 'Table'[payment date] < CurrentDate )
    VAR PreviousRecord =
    TOPN ( 1, TableBefore, 'Table'[payment date] )
    RETURN
    MAXX ( PreviousRecord, 'Table'[value] )

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi RSSILVA_22 

if you're looking for a calculated column then please try

Previous Payment =
VAR CurrentDate = 'Table'[payment date]
VAR CurrentTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[cod_client] ) )
VAR TableBefore =
FILTER ( CurrentTable, 'Table'[payment date] < CurrentDate )
VAR PreviousRecord =
TOPN ( 1, TableBefore, 'Table'[payment date] )
RETURN
MAXX ( PreviousRecord, 'Table'[value] )

  • RSSILVA_22's avatar
    RSSILVA_22
    Icon for Helper I rankHelper I
    3 years ago

    very good
    thanks.