Adding Calculated Column with the latest Item

[Daniel48]

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                 ABD

X01 01/01/2019     ABC                 ABD

X01 01/01/2020     ABC                 ABD

X01 01/01/2021     ABC                 ABD  

X01 01/01/2022    ABD                  ABD

X02 01/01/2023 

X03

I am trying to get the new column to pull the latest item 01/01/2022 for category X01. I can get it for the whole data set but not for each category

Thank you in Advance,

[quantumudit · Accepted solution]

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 😊

[Daniel48]

Worked perfectly. Thank you for your help!

[quantumudit]

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]

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

[quantumudit]

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]

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

[quantumudit]

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

[Daniel48]

Ok, I created a new post Pulling most update value and excluding blanks - Microsoft Fabric Community

[quantumudit]

Hey @Daniel48 

I have provided the solution in the new ticket, Kindly give that a shot 👍

Best Regards,
Udit

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