Forum Discussion

lasmithfla's avatar
lasmithfla
Helper I
4 years ago
Solved

Need help pulling Max date based on a measure

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

 

 

General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

2 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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