Calculate Date related to MAX

[Anonymous]

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.

[affan · Accepted solution]

Hi @Anonymous

You can download the pbix file here.

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

[Anonymous]

Hi @affan,

Thank you very much! its working.

Best Regards.