Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Is it possible to have this look only non blanks. For example the newest item has a blank value but the year prior has a value. I would like to pull the prior if there is no current
Solved! Go to Solution.
Hey @Daniel48
Here is the DAX formula to achieve the desired result; it will retrieve the current value for the most recent year with a non-blank value.
Latest Value =
VAR _currentCategory = Data[Category]
VAR _maxDate =CALCULATE(
MAX(Data[Date]) ,
FILTER ( Data, Data[Category] = _currentCategory && Data[Current] <> BLANK())
)
VAR _leastVal =
CALCULATE (
MAX ( Data[Current] ),
FILTER ( Data, Data[Date] = _maxDate && Data[Category] = _currentCategory )
)
RETURN
_leastVal
With just a minor tweak, I need to add an AND condition.
Below is a snapshot with the highlighted change in the formula and the resulting dataset. I tested the formula by setting the X01 latest year's current data to blank, and it worked.
Give it a try and let me know.
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
Hi,
Share data in a format that can be pasted in an MS Excel file. Show the expected result very clearly.
Hey @Daniel48
Here is the DAX formula to achieve the desired result; it will retrieve the current value for the most recent year with a non-blank value.
Latest Value =
VAR _currentCategory = Data[Category]
VAR _maxDate =CALCULATE(
MAX(Data[Date]) ,
FILTER ( Data, Data[Category] = _currentCategory && Data[Current] <> BLANK())
)
VAR _leastVal =
CALCULATE (
MAX ( Data[Current] ),
FILTER ( Data, Data[Date] = _maxDate && Data[Category] = _currentCategory )
)
RETURN
_leastVal
With just a minor tweak, I need to add an AND condition.
Below is a snapshot with the highlighted change in the formula and the resulting dataset. I tested the formula by setting the X01 latest year's current data to blank, and it worked.
Give it a try and let me know.
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
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |