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
lasmithfla
Helper I
Helper I

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
lasmithfla_1-1649697644403.png

 

lasmithfla_0-1649697536069.png

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
HotChilli
Super User
Super User

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.

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.