Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table like this. Notice the blank values towards the bottom.
I would like to get the value of the latest date, but the value can't be null.
My DAX formula is bringing back the value of the latest date (19/12/2021) which is null, however I want to bring back the latest non-null value, which is for the date 21/11/2021.
Here is what I have tried so far:
Latest Value =
CALCULATE(
// get sum of value column
SUM('Table1'[Value]),
// where value is not blank, and date is max date
'Table1'[Value] <> BLANK() && Table1[Date] = MAX(Table1[Date])
)
I get the value: null / blank
I thought this should bring back the figure 305? Because my conditions are:
where value is not null AND where date = max date
Shouldn't the max date now be 21/11/21 because the nulls have been removed?
Another piece of DAX I've tried, using the filter function.
Latest Value = CALCULATE(
SUM('Table1'[Value]),
FILTER(ALL('Table1'),
'Table1'[Value] <> BLANK()
&&
'Table1'[Date] = MAX('Table1'[Date]))
I get the same result: null
Where am I going wrong? I think it's something to do with my max date section.
Unfortunately all file hosters are blocked in work, so I can't share this dummy file. However I hope I have simplified the data enough to just quickly get the idea.
If there is an attachment button, please let me know because I can't find it.
Many thanks
Solved! Go to Solution.
Hi @Anonymous ,
1.If one date has one value, you could simply use LASTNOTBLANK():
Measure = LASTNONBLANK('Table'[Value],MAX('Table'[Date]))
2. If you want to get the sum of the last date, please try to get the last and value<> blank date firstly and then sum:
Measure 2 = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]=MAXX(FILTER('Table',[Value]<>BLANK()),[Date])))
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
1.If one date has one value, you could simply use LASTNOTBLANK():
Measure = LASTNONBLANK('Table'[Value],MAX('Table'[Date]))
2. If you want to get the sum of the last date, please try to get the last and value<> blank date firstly and then sum:
Measure 2 = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]=MAXX(FILTER('Table',[Value]<>BLANK()),[Date])))
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, this worked.
@Anonymous ,
Max non blank date
Maxx(filter(Table, not(isblank(Table[Value])) , Table[Date]))
or
Maxx(filter(allselected(Table), not(isblank(Table[Value])) , Table[Date]))
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |