Forum Discussion

apatwal's avatar
apatwal
Icon for Helper III rankHelper III
4 years ago
Solved

Calculate Last Transaction Records

Hi,   Within Power BI, I have a below table and I want to create another column which calculates Last Margin % for every records.    If the customer A buys product A12 then for its Last Mar...
DAX Calculation
DAX Commands and Tips
Guidance
Help required
Help with DAX
  • amitchandak's avatar
    amitchandak
    4 years ago

    apatwal , A new column and new measure

     

    new column =
    var _max = maxx(filter(table, [Customer] = earlier([Customer]) && [Product] =earlier([Product]) && [Date] < earlier([Date])), [Date])
    return
    maxx(filter(table, [Customer] = earlier([Customer]) && [Product] =earlier([Product]) && [Date] _max), [Margin %])

     


    new measure =
    var _max = maxx(filter(allselected(table), [Customer] = max([Customer]) && [Product] =max([Product]) && [Date] < max([Date])), [Date])
    return
    maxx(filter(allselected(table), [Customer] = max([Customer]) && [Product] =max([Product]) && [Date] _max), [Margin %])

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi apatwal ,

 

1.Yes, I have modified my previous reply.

2. I think amitchandak 's method is the most effective ,if there is a large data, you may try to apply some filters (like set a date period by using slicer) to reduce the query.

 

Best Regards,
Eyelyn Qin