Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
89 | |
80 | |
60 | |
59 | |
58 |
User | Count |
---|---|
157 | |
118 | |
101 | |
76 | |
68 |