cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fallaha Frequent Visitor
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
affan Established Member
Established Member

Re: Calculate Date related to MAX

Hi @fallaha

 

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

2 REPLIES 2
affan Established Member
Established Member

Re: Calculate Date related to MAX

Hi @fallaha

 

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

fallaha Frequent Visitor
Frequent Visitor

Re: Calculate Date related to MAX

Hi @affan,

Thank you very much! its working.

Best Regards.