Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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
affan
Solution Sage
Solution Sage

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

View solution in original post

2 REPLIES 2
affan
Solution Sage
Solution Sage

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
Not applicable

Hi @affan,

Thank you very much! its working.

Best Regards.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.