cancel
Showing results for
Did you mean:
Highlighted
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

Frequent Visitor

## Re: Calculate Date related to MAX

Hi @affan,

Thank you very much! its working.

Best Regards.