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
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,
Solved! Go to 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 😊
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 😊
Worked perfectly. Thank you for your help!
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
Ok, I created a new post Pulling most update value and excluding blanks - Microsoft Fabric Community
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |