Forum Discussion
Helper IPulling most update value and excluding blanks
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
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
3 Replies
- quantumudit
Super User
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
Daniel48Thank you that worked!
- Ashish_Mathur
Hi,
Share data in a format that can be pasted in an MS Excel file. Show the expected result very clearly.
