Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there
I'm trying to create a calculated column that returns the last non blank value by a given date. I've tried several DAX options but can't quite get this working. See data below - in this example I would expect a value of -75.71 for 10/11/21.
Thanks in advance
Faye
Solved! Go to Solution.
Hi @FayeB1901
Try this:
Measure=
Var _A = max(table[Date])
If(isblank(max(table[value])),
calculate(max(table[value]),filter(all(table),table[Date]<_A&&table[value]<>blank())),
max(table[value])
)
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi Vahid
No unfortunately no good - not sure what is going on here with the calculation!
Hi @FayeB1901
Try this:
Measure=
Var _A = max(table[Date])
If(isblank(max(table[value])),
calculate(max(table[value]),filter(all(table),table[Date]<_A&&table[value]<>blank())),
max(table[value])
)
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi Vahid
No unfortunately no good - not sure what is going on here with the calculation!
Try this:
Meas1ure =
VAR _A =
MAX ( table[Date] )
VAR _B =
CALCULATE (
MAX ( table[Date] ),
FILTER ( ALL ( table ), table[Date] < _A && table[value] <> BLANK () )
)
RETURN
IF (
ISBLANK ( MAX ( table[value] ) ),
CALCULATE ( MAX ( table[value] ), FILTER ( ALL ( table ), table[Date] = _B ) ),
MAX ( table[value] )
)
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
@VahidDM any ideas on how to calculate as a column? Those values are Lat/Lon that I need to plot on the map & ARCGIS won't work with a measure ...
Try this to add a column:
Column=
VAR _A =
[Date]
VAR _B =
CALCULATE (
MAX ( table[Date] ),
FILTER ( ALL ( table ), table[Date] < _A && table[value] <> BLANK () )
)
RETURN
IF (
ISBLANK ( [value] ),
CALCULATE ( MAX ( table[value] ), FILTER ( ALL ( table ), table[Date] = _B ) ),
[value] )
)
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
@VahidDM no not quite - seems like a filter context thing, like it's using a result not from the relevant ID maybe?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |