Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vehau1
Helper I
Helper I

Get value from row in the same table based on last date

This should be trivial, but I'm dragging the hairs off my head! I can't get to display the most recent value!!!!!! why do I get every other value when I try to filter on last nonblank date?

 

I have a table, let's say its like this:

 

Column  A has dates

Column B has values. Some values are blank, others are not.

 

I want to display the last non-blank value of B, in a card visual.

 

I can't seem to make it happen!!!!! The values in column B are Decimals. What the f should I do?

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @vehau1

Create measures

column B = SUM(Sheet2[columnb])

last non-blank date = CALCULATE(MAX(Sheet2[date]),FILTER(ALLSELECTED(Sheet2),[column B]<>BLANK()))

FLAG = IF([last non-blank date]=MAX(Sheet2[date]),1,0)

last non-blank value = CALCULATE(MAX(Sheet2[columnb]),FILTER(ALL(Sheet2),[FLAG]=1))

2.png

 

Best Regards

Maggie

View solution in original post

5 REPLIES 5
AnkitBI
Solution Sage
Solution Sage

Please give this a try. I am filtering base table to fetch all Non-Blank Rows based on Values, Then, From filtered table, extracting Max(Date)

Measure 3 = CALCULATE(max('Table'[date]),filter('Table',not(isblank('Table'[val]))))

@vehau1 Please mark this as Solution if any of the provided solution resolved your Problem

v-juanli-msft
Community Support
Community Support

Hi @vehau1

Create measures

column B = SUM(Sheet2[columnb])

last non-blank date = CALCULATE(MAX(Sheet2[date]),FILTER(ALLSELECTED(Sheet2),[column B]<>BLANK()))

FLAG = IF([last non-blank date]=MAX(Sheet2[date]),1,0)

last non-blank value = CALCULATE(MAX(Sheet2[columnb]),FILTER(ALL(Sheet2),[FLAG]=1))

2.png

 

Best Regards

Maggie

Great, thanks! Wasn't as straight forward as I thought, but I will study the codes.

Anonymous
Not applicable

Hi,

 

This code worked for me:

LastValue = LASTNONBLANK(Table1[Value],"")

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.