Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
MathisD
Regular Visitor

Create a new column with the date of the last identical operation (according to certain criteria).

Hello everyone,
I would like to create a new column in powerBI that for each row (transaction) associates me the value 0 or 1 depending on:
1: if this transaction is the last transaction (to date) of the same type (that means same shopper email and amount) and it has as a response: REFUSED
0 otherwise

MathisD_0-1638887562970.png



Format in Power Bi/
Creation Date: Date/Time
Amount = Decimal number

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@MathisD,

 

Try this calculated column:

 

New Column = 
VAR vAmount = Table1[Amount]
VAR vEmail = Table1[Shopper Email]
VAR vTable =
    FILTER ( Table1, Table1[Amount] = vAmount && Table1[Shopper Email] = vEmail )
VAR vMaxDate =
    MAXX ( vTable, Table1[Creation Date] )
VAR vResult =
    IF (
        Table1[Creation Date] = vMaxDate
            && Table1[Acquirer Response] = "REFUSED",
        1,
        0
    )
RETURN
    vResult

 

DataInsights_0-1638971395519.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
MathisD
Regular Visitor

Thank you very much for you help 🙂

DataInsights
Super User
Super User

@MathisD,

 

Try this calculated column:

 

New Column = 
VAR vAmount = Table1[Amount]
VAR vEmail = Table1[Shopper Email]
VAR vTable =
    FILTER ( Table1, Table1[Amount] = vAmount && Table1[Shopper Email] = vEmail )
VAR vMaxDate =
    MAXX ( vTable, Table1[Creation Date] )
VAR vResult =
    IF (
        Table1[Creation Date] = vMaxDate
            && Table1[Acquirer Response] = "REFUSED",
        1,
        0
    )
RETURN
    vResult

 

DataInsights_0-1638971395519.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.