Need help pulling Max date based on a measure

[lasmithfla]

I don't know how to attach the sample powerbi file that I made up for this example; I can't post real data.

I'm able to identify the max sales but need to be able to also extract the date of the sales.  Note the Total Sales is based on a measure - it's not in the row level data of the table to identify.

Measure to get the total sales = 

TotalSales = Sum(SalesTable[Sales])

Measure to identify the Max Sale amount = 

MaxSales = VAR _TempSalesTable = CALCULATETABLE(Values(SalesTable[Date]))

VAR _MaxSales = Calculate(Maxx(_TempSalesTable,SalesTable[TotalSales]),ALLSELECTED(SalesTable[Store Number]))

Return _MaxSales

What I'm missing is the measure to now identify the date that goes along with the Max Sale amount that was identified

[Anonymous · Accepted solution]

HI @lasmithfla,

You can try to use summarize and max functions to achieve your requirement:

MaxSales =
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( SalesTable ),
        [Date],
        "Total", SUM ( SalesTable[TotalSales] )
    )
RETURN
    MAXX ( FILTER ( summary, [Total] = MAXX ( summary, [Total] ) ), [Date] )

Regards,

Xiaoxin Sheng

[HotChilli]

I think that the MaxSales measure looks a bit overcomplicated.

--

Try putting date and TotalSales measure in a table visual.  Create a ranking measure similar to this:

MeasureRank = 
RANKX(ALL(SalesTable[Date]), [TotalSales])

and put the MeasureRank in a visual filter and set it to 1.

For simple visuals like this, a measure filter can work.

---

If you post your data as text next time, you'll get a faster answer.