Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to Get value for last transaction?

Hi guys

I have a table with value and inventory I want to multiply the value into inventory based on the last transaction that happened to this stock

"more clarification"

Inventory * Value (in the last inventory transaction)

 

Reports
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous 

     

    If you want to have a calculated column, here are two ways:

    Column = MAXX(TOPN(1,FILTER('Table','Table'[Transaction Time]<EARLIER('Table'[Transaction Time])),'Table'[Transaction Time],DESC),'Table'[Value])

    Or

    Column 2 = 
var __previousTranctionTime = MAXX(FILTER('Table','Table'[Transaction Time]<EARLIER('Table'[Transaction Time])),'Table'[Transaction Time])
return
MAXX(FILTER('Table','Table'[Transaction Time]=__previousTranctionTime),'Table'[Value])

     

    And here is one way with measure

    Last Value = 
var __previousTranctionTime = MAXX(FILTER(ALL('Table'),'Table'[Transaction Time]<SELECTEDVALUE('Table'[Transaction Time])),'Table'[Transaction Time])
return
SUMX(FILTER(ALL('Table'),'Table'[Transaction Time]=__previousTranctionTime),'Table'[Value])

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

6 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous 

     

    If you want to have a calculated column, here are two ways:

    Column = MAXX(TOPN(1,FILTER('Table','Table'[Transaction Time]<EARLIER('Table'[Transaction Time])),'Table'[Transaction Time],DESC),'Table'[Value])

    Or

    Column 2 = 
var __previousTranctionTime = MAXX(FILTER('Table','Table'[Transaction Time]<EARLIER('Table'[Transaction Time])),'Table'[Transaction Time])
return
MAXX(FILTER('Table','Table'[Transaction Time]=__previousTranctionTime),'Table'[Value])

     

    And here is one way with measure

    Last Value = 
var __previousTranctionTime = MAXX(FILTER(ALL('Table'),'Table'[Transaction Time]<SELECTEDVALUE('Table'[Transaction Time])),'Table'[Transaction Time])
return
SUMX(FILTER(ALL('Table'),'Table'[Transaction Time]=__previousTranctionTime),'Table'[Value])

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you very much, I got the result🌹

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    Anonymous you can use LASTNONBLANKVALUE DAX measure to get the value

     

    Last Value = 
LASTNONBLANKVALUE ( CalendarTable[Date], SUM ( YourTable[Value] ) )

     

    ✨ Follow us on LinkedIn

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

       same result 

       

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Share the link from where i can download your PBI file.  Clearly show where you want to generate the result.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      thank you brother