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.
Hello,
I created a measure using the formula to get the last non-blank value in a column.
LASTNONBLANKVALUE('Table1'[date], MAX('Table1'[value]))
I would like to compare this value to the previous row, or the second last non-blank value. Is there a formula to do this? Thank you.
Solved! Go to Solution.
@Anonymous
Thank you for the articles. I was trying the other solutions offered on this post and they did not work but I think I have a basic misunderstanding on what LASTNONBLANK and LASTNONBLANKVALUE were doing. I was getting what looked like the right answer with my formula, but I was going about it the wrong way. I appreciate the resources.
Here is one way to do it. I split it into multiple variables to make it easier to follow.
SecondLastBlankValue =
VAR summary =
FILTER (
Table1,
NOT (
ISBLANK ( Table1[Value] )
)
)
VAR top2 =
TOPN (
2,
summary,
Table1[Date], DESC
)
VAR top1 =
TOPN (
1,
top2,
Table1[Date], ASC
)
RETURN
MAXX (
top1,
Table1[Value]
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@stalerik , Try like
calculate(LASTNONBLANKVALUE('Table1'[date], MAX('Table1'[value])), filter('Table1', 'Table1'[date] <max('Table1'[date])))
or
calculate(LASTNONBLANKVALUE('Table1'[date], MAX('Table1'[value])), filter(all('Table1'), 'Table1'[date] <max('Table1'[date]))) //or allselected , depending on need
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |