Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Daniel48
Helper I
Helper I

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                 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,

 

 

 

1 ACCEPTED SOLUTION
quantumudit
Skilled Sharer
Skilled Sharer

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:

pbi_solution_dax.jpg

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

 

Thanks 😊

 

 

 

View solution in original post

9 REPLIES 9
quantumudit
Skilled Sharer
Skilled Sharer

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:

pbi_solution_dax.jpg

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

 

Thanks 😊

 

 

 

Worked perfectly. Thank you for your help!

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

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

 

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

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

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

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.