cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate Date related to MAX

Hi Experts,

I have this sales table, with Date, Sold Product(Quantity), UserID and ProductID.

Date              Sold Product        UserId   ProductID

-----              --------------         -------   -----------

2019-01-01          10                     1              1

2019-01-01          15                     1              2

2019-01-01           5                      2              1

2019-01-05          10                     1              1

2019-01-05           3                      2              1

2019-01-05           3                      2              2

2019-01-10          20                     1              2

And I would like to show a table with UserId ,the maximum Sales (regardless of the ProductID) and the date of MaxSales, like this:

UserID     MaxSales                  Date Of MaxSales
-------    --------------------     ----------------------------
1                    25                           2019-01-01
2                    6                             2019-01-05

First I created a measure called TotalSold to calculate the Sum of Sold Product:

TotalSold = SUM(Sales[Sold Product])

Then I created a masure called MaxSales to calculate the max of TotalSold

MaxSales =
MAXX (
SUMMARIZE (
Sales,
Sales[Date],
Sales[UserID],
"MaxSales", [TotalSold]
),
[TotalSold]
)

But Im not able to calculate the date related to the MaxSales, could you please help me!
Here I attach a onedrive link that contains the pbix file:

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

## Re: Calculate Date related to MAX

Hi @fallaha

Actually you have to add one more measure for the date.

```MaxSaleDate =
CALCULATE(FIRSTDATE(DimDate[Date]),
FILTER(VALUES(DimDate[Date]),
[TotalSold]=
CALCULATE([MaxSale],
VALUES(DimDate[Date])))
)```

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

2 REPLIES 2
Established Member

## Re: Calculate Date related to MAX

Hi @fallaha

Actually you have to add one more measure for the date.

```MaxSaleDate =
CALCULATE(FIRSTDATE(DimDate[Date]),
FILTER(VALUES(DimDate[Date]),
[TotalSold]=
CALCULATE([MaxSale],
VALUES(DimDate[Date])))
)```

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

Highlighted
Frequent Visitor

## Re: Calculate Date related to MAX

Hi @affan,

Thank you very much! its working.

Best Regards.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 101 members 1,395 guests
Recent signins: