DAX: Highest Value by Date

[Darko_Giac]

Hi all,

I'm currently attempting to work through a DAX formula that will group my highest value and the date that highest value was submitted.

My data set consists of Date Submitted, Bid, Room and Employee columns. The basic idea is that employees are bidding on certain rooms to rent out for the day, however, if two employees bid the same amount, I would need a way to distinguish a winner (and I am currently attempting to filter on date submitted, which has a time stamp, so that the employee who bid first would win the room).

I've attached a sample workbook to hopefully clarify the issue.

SampleWorkbook

Any suggestions would be greatly appreciated!

[Anonymous]

Hi.

Is this to be a measure or calculated column? Excel sucks when it comes to authoring DAX formulas - the formula bar is just a pain to use :((( It's much better to send .pbix files in the future, just so you know.

Best

Darek

[Darko_Giac]

Hey hey, @Anonymous  this would be a calculated column 🙂

I'm able to return the earliest date of the two bids, but I would like to only return the name of the person who put in the earliest bid.

Here is an updated .pbix workbook.

Sample PBix Workbook

[Anonymous · Accepted solution]

Hiya.

Here's your column formula:

Winning Bidder = 
var __currentRoom = RealTimeData[Room]
var __recordsWithCurrentRoom =
    FILTER(
        RealTimeData,
        RealTimeData[Room] = __currentRoom
    )
var __winnerRecord =
    TOPN(
        1,
        __recordsWithCurrentRoom,
        RealTimeData[bid],
        DESC,
        RealTimeData[DateSubmit],
        ASC
    )
var __winner =
    MAXX(
        __winnerRecord,
        RealTimeData[employee]
    )
return
    __winner

Best

Darek

[Darko_Giac]

Thanks so much @Anonymous ! Worked like a charm 🙂