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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PoojaG
Advocate I
Advocate I

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
Anonymous
Not applicable

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

3 REPLIES 3
rajibshovan
Frequent Visitor

Thanks a lot for the solution. It served my purpose as well.

tarunsingla
Solution Sage
Solution Sage

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.
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.