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.
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]))
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |