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 Margin % is null and if same customer (i.e. A) buys same product (i.e. A12) then its Margin % will 30%. So my result table will be as below.

 

I want to write DAX for this. Can some one help me on this?

 

 

 

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 %])

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    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 ,

     

    Firstly, you could follow amitchandak 's suggestion.

     

    Or try:

     

    Column = 
var _previousDate= MAXX(FILTER('Table',[Product]=EARLIER('Table'[Product]) && [Customer]=EARLIER('Table'[Customer]) && [Date]<EARLIER('Table'[Date])),[Date]) 
return LOOKUPVALUE('Table'[Margin %],[Customer],[Customer],[Date],_previousDate)

     

     

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

      there is a bug in this code.

      it always gets the max value of Margin%, instead of the Margin% of the max date.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi wdx223_Daniel ,

         

        Sorry for my mistake, you are right !

        I have modified my previous reply. Thanks for reminding me😀

         

        Best Regards,
        Eyelyn Qin

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

    Calculated Column=MAXX(TOPN(1,FILTER(Table,Table[Product]=Earlier(Table[Product])&&Table[Date]<EARLIER(Table[Date])),Table[Date]),Table[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