Forum Discussion

Daniel48's avatar
Daniel48
Helper I
2 years ago
Solved

Adding Calculated Column with the latest Item

Hello,   I have tried many different ways and I can not seem to make this work.   I have a list of items   Category    Date  Current           New Colum X01  01/01/2018    ABC                 A...
Need Help
  • quantumudit's avatar
    quantumudit
    2 years ago

    You can use the following DAX formula to create a calculated column:

     

    Latest Value = 
VAR _currentCategory = Data[Category]
VAR _maxDate =
    CALCULATE (
        MAX ( Data[Date] ),
        FILTER ( Data, Data[Category] = _currentCategory )
    )
VAR _leastVal =
    CALCULATE (
        MAX ( Data[Current] ),
        FILTER ( Data, Data[Date] = _maxDate && Data[Category] = _currentCategory )
    )
RETURN
    _leastVal

     

    Here is the solution screenshot:

    Make sure to leave a kudo if it resolves your issues...

     

    Thanks 😊

     

     

     

quantumudit's avatar
quantumudit
Super User
2 years ago

Happy to help 😊

 

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Daniel48's avatar
Daniel48
Helper I
2 years ago

Is it possible to ignore blanks a couple of my items have nothing for the current year but do for the prior

 

  • quantumudit's avatar
    quantumudit
    Super User
    2 years ago

    Indeed, the ISBLANK() function in DAX can be utilized in conjunction with an IF() function to either ignore or substitute blank values as needed.

    • Daniel48's avatar
      Daniel48
      Helper I
      2 years ago

      How would it go to last prior value? Mine is not seeming to work. 

      • quantumudit's avatar
        quantumudit
        Super User
        2 years ago

        Maybe create a new ticket and show exactly what you want to achieve and then, I will check it out