cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Terp
Frequent Visitor

Return Corresponding Date of 'Max Value' (like Match/Offset in Excel)?

Being a total rookie, I am unsure if there is a setting/toggle I can flick to get at this, but I am trying to display a 'card' with the max value AND the date of that value. Getting the MAX value is easy, of course, but can't figure out how to get the date of that value...assuming I would use two cards and overlay them to achieve the style I am going for, but just not sure of the DAX necessary to get the date.

 

In Excel, I would use MATCH/OFFSET or vlookups to find the max value, offset to get the corresponding date header, and use that value.  In PowerBI, I am clueless.

 

Example:

 

trade.jpg

So I have a card that shows the max value of the 'Total U.S. Exports' column; for the timeperiod in the screenshot, it would display "$86,192".  I would then like to show the date of this 'record-setting/highest' value.

 

Is there an inherent measure that gets at this, or do I need to seek a little DAX to do this? 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User II
Super User II

@Terp 

Try it with something like this measure.

Top Period = 
FIRSTNONBLANK(TOPN(1,VALUES('Table'[Period]),CALCULATE(SUM('Table'[Total US Exports]))),1)

jdbuchanan71_0-1623515814944.png

 

 

View solution in original post

5 REPLIES 5
Terp
Frequent Visitor

If I could ask a follow-up, as the FIRSTNONBLANK usage just hit me; this works ONLY because I have the date/period in the first column, correct? If the period were in the middle of all the other columns, this approach wouldn't work?

 

(I'll play with that now, but thought I would ask in hopes of better understanding some of these functions while I wait for a book on DAX to arrive). 🙂

 

Thanks again.

Not really, no.  The first part of the measure 

TOPN (
        1,
        VALUES ( 'Table'[Period] ),
        CALCULATE ( SUM ( 'Table'[Total US Exports] ) )
    )

 Gives us the top values from a table based on an expression.

How many values? 1

What table? VALUES('Table'[Period]) gives us a table of the unique items in the [Period] column.

What expression? SUM('Table'[Total US Exports]).

Terp
Frequent Visitor

Thank you. Thank you. Thank you! 

 

It was a long road to get there, but once I followed your example more closely, I got it to work. 🙂

 

I originally had...

VALUES('Table'[Period].[Month])

 ...and it was returning a month (albeit the wrong month), so I tried to add a compound value to get the month and the year, but no luck. 

 

So I created a new measure to return the year and it worked fine. HOWEVER, I then noticed the [period].[month] was returning the WRONG month! I could not figure out why, but finally looked more closely as your example, removed the [month] from the code, and all was well! I got the month AND year with one measure now....good to go.

 

I don't understand why the use of 'FIRSTNONBLANK', but I won't question it...and 'FIRSTNONBLANK' will solve another probablem I have been thinking about (how to return the last/most recent value, so charts update and reflect the latest data without me going in and changing the filter to the last month for which I have data). 

 

Thanks again!

The outlined portion of the measure returns a table of values.  In this case it is a table with 1 row but DAX doesn't care and gives the error 'a table of multiple values was supplied..'  FIRSTNOBLANK converts that into a scalar value based on the  expression, in this case just 1 so it just gives us the single row in the outlined table.

jdbuchanan71_0-1623546293637.png

 

jdbuchanan71
Super User II
Super User II

@Terp 

Try it with something like this measure.

Top Period = 
FIRSTNONBLANK(TOPN(1,VALUES('Table'[Period]),CALCULATE(SUM('Table'[Total US Exports]))),1)

jdbuchanan71_0-1623515814944.png

 

 

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors