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

Return max of value and corresponding date for the selected date period (slicer)

hello,

I'm trying to create a measure which should retun the max of a value in the selected time period and also return the corresponding date on which that max value appeared. 

Below is the screenshot of the sample data and highlighted row is the outcome I expect as separate measures. 

The time period selected is Jan 2019. I was able to create a measure to return the max of value but not able to get the corresponding date. Please help! Thank you!Max of value.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Return max of value and corresponding date for the selected date period (slicer)

Assuming you have a date table, you can do the following:

Max = max (Table1[Value] )
Date of Max = 
Var __SelectedMax =
 CALCULATE(
   MAX( Table1[Value]),
    ALLSELECTED(DimDate)
)

RETURN 

CALCULATE(
    VALUES(DimDate[Date]),
        FILTER(
            ALL( DimDate),
            [Max] = __SelectedMax )
)

and if you are so inclined you can put it all together for a card:

Measure for a Card = "Max of "&[Max]&" Occured on "& [Date of Max]

Max value with date.png

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Return max of value and corresponding date for the selected date period (slicer)

Assuming you have a date table, you can do the following:

Max = max (Table1[Value] )
Date of Max = 
Var __SelectedMax =
 CALCULATE(
   MAX( Table1[Value]),
    ALLSELECTED(DimDate)
)

RETURN 

CALCULATE(
    VALUES(DimDate[Date]),
        FILTER(
            ALL( DimDate),
            [Max] = __SelectedMax )
)

and if you are so inclined you can put it all together for a card:

Measure for a Card = "Max of "&[Max]&" Occured on "& [Date of Max]

Max value with date.png

View solution in original post

tarunsingla Established Member
Established Member

Re: Return max of value and corresponding date for the selected date period (slicer)

Try this calculated measure:

 

Max Value Date =
var _max = MAX(Table1[ValueField])
RETURN CALCULATE(MIN(Table1[DateField]), Table1[ValueField] = _max)
 
Gives you the first date in the selected timeframe, that has a maximum value.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 316 members 2,886 guests
Please welcome our newest community members: