Max of a column based on two other Columns

[Anonymous]

Hi,

I want to find the max of the "Sold" column based on the max "ReadingDate" for each product category. Please, I would like to use DAX for this. For example, Beans would return 9 as the max based on the date time field. Thanks so much.

ReadingDate	Prroduct	Sold
7/1/2020 : 7:37:44 PM	Beans	1
7/1/2020 : 7:45:29 AM	Beans	0
7/3/2020 : 7:37:44 PM	Rice	0
7/1/2020 : 5:37:44 AM	Fruit	3
7/16/2020 : 7:37:44 AM	Rice	0
7/5/2020 : 10:00:44 AM	Yam	4
7/16/2020 : 04:37:44 PM	Yam	5
7/8/2020 : 11:37:14 PM	Fruit	3
7/1/2020 : 5:37:13 AM	Yam	1
7/15/2020 : 7:37:44 PM	Fruit	0
7/4/2020 : 2:02:12 PM	Beans	1
7/5/2020 : 7:37:44 AM	Beans	0
7/5/2020 : 7:37:44 PM	Rice	2
7/10/2020 : 5:27:09 PM	Beans	9
7/16/2020 : 7:37:44 PM	Rice	0
7/16/2020 : 6:20:11AM	Rice	3
7/17/2020 : 7:37:44 PM	Rice	4
7/16/2020 : 12:37:44 PM	Fruit	8
7/16/2020 : 9:37:40 AM	Fruit	9

[mahoneypat · Accepted solution]

Please try this expression in a measure.  Note that I had to change your ReadingTime column in the query editor to replace " : " with " ", so that I could change it to DateTime type.  You will need to change the name of the table to your actual name.

Latest Count =
VAR maxdatetime =
    MAX ( 'Product'[ReadingDate] )
RETURN
    CALCULATE ( MAX ( 'Product'[Sold] ), 'Product'[ReadingDate] = maxdatetime )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi @mahoneypat 

Thanks for you prompt response. I ran your code but it only gave me one value for all the products. Looking at your code, i'm assuming that Product is the name of the table but at what point did you factor in the fact that there is a product category column containing (Beans , rice etc) in the data? I'm guessing that's why it didn't give the wanted result. Thanks.

[Anonymous]

@mahoneypat 

I am so SORRY. Your code worked like a charm. I was in the wrong table that's why and i didn't need to change the colon on the datetime field to space and it still worked. Thanks so much. 🤗

[Anonymous]

@Fowmy 

Hi! Is there a way around this? Please, i need your help. thanks