Only show column value from top row of sorted data

[boclifton]

Hi everyone,

I have what seems to be a simple question, but I'm new to Power BI and can't quite get a handle on it.  I have a table of data with a Date in a column; and a non-SUM'd value, a record of current failures in the data, so it can vary from row to row.  I just want to get the most recent value from the current failures column based on the Date column.

Example:

Date CurrentFailures

10/1/2016 33445

10/15/2016 500

10/30/2016 10666

11/1/2016 555

I'd like the measure (or column...?) to return 555.  Thanks in advance for any assistance!

[Vvelarde]

@boclifton

If you want to show the failures of last date in your table.

you can use the next measure:

CurrentFailure-in-LastDate =
CALCULATE ( VALUES ( Table1[CurrentFailures] ), LASTDATE ( Table1[Date] ) )

[Stachu]

if data is sorted then the 'Keep bottom rows' may work for you

in the query editor, go to 

Home > Keep Rows > Keep Bottom Rows

[boclifton]

The data isn't sorted in the query.  Should I apply sorting in the query area? I thought that was supposed to be handled in the Report area by the visuals.  

(Sorry, like I said, I'm very new to Power BI...)

EDIT:  Also, I suppose I should have mentioned, there's other data in the table.  Not just the date and failures.  It's a table of transaction info for data transfers and CurrentFailures is just one of the columns.

[Stachu]

do you need the value in the front end on query editor?

if the former then @Vvelarde solution should work, if you need it in query editor then how do you plan to use it further? is it a parameter for some calculation?

[boclifton]

@Stachu Really, I just need the value for display as a "CurrentItemsFailed" column that will only have one value for each client.  I want to click the slicer I have in place that lists all my clients, then get a single result for "CurrentItemsFailed". 

@Vvelarde I think this is almost it.  Power BI complained that it had duplicate entries for LastDate so it couldn't be used.  I have multiple clients dumping data into this table, so I still need it to respond to slicers for clientID, as well.  I've included a screenshot of the top couple of rows in the table.

Thanks for all your help, guys!  I feel like it's almost there.

[Vvelarde]

@boclifton

Let me ask you some questions;

When you have two or more row with same last date what result do you want to show in Current Failures? SUM, Average..?

[boclifton]

@Vvelarde That should only happen when I have no clients selected, so a SUM should be fine.

Also, most entries are going to have 4 entries per day (or more).  Will LASTDATE go to the latest date/time, or just the date portion?  I.E. if I have 3 entries on 10/1, will it complain, or will it display the latest time?

[Vvelarde · Accepted solution]

@boclifton

Add Before The IF(HasOneValue([ClientTable[ClientColumn]); the measure)

So when is no selected a client the measure don't show a value.

The LastDate take all the dates with the last date. if you have three rows take all of them.

[boclifton]

Thanks to your help (both of you!), I was able to come up with this.

Incomplete = IF(HASONEVALUE(Clients[ClientName]), CALCULATE(VALUES(Transactions[CurrentItemsFailed]), LASTNONBLANK(Transactions[EndTime], Transactions[EndTime])))

This seems to be doing the trick, and I've verified the data against my Azure DB.  Thanks guys!

[Stachu]

yes, in the Query area

alternatively, if the field format is Date then you can apply the Date filter 'Is latest' in PowerQuery - this may return multiple values though if one date can have multiple rows