SHOW COMPONENT VALUES FROM SPECIFIC DATE OF MAX RESULT FROM SUMMED VALUES

[bbaker1987]

I'm trying to get the date for the maximum summed volume over a period of time and then display the returned summed max along with the components from that formula. I can successfully get the date and the max value but when I go to show all of the components that make up the maximum summed value, the measure applies the date for each components individual maximum sum. I cannot, for the life of me, find a way to make the date only take the date from the summed whole.

date	prod	qty
2/27/2023	a	15
2/27/2023	b	5
2/27/2023	c	2
2/27/2023	a	15
2/27/2023	b	10
2/27/2023	c	5
2/28/2023	a	15
2/28/2023	b	10
2/28/2023	c	10
2/28/2023	a	10
2/28/2023	b	20
2/28/2023	c	10
Currently
2/28/2023	75	total
	30	a
	30	b
	20	c
Expected	Max Value total
2/28/2023	75	total
	25	a
	30	b
	20	c

Attached is a dumbed down version of my dataset. I do have a separate 'Dates' table and I currently have a measure that is summing the individual prod values.

[bbaker1987]

Dangit, my mistake (copy paste error).

You're right, it should be 02/28/23. I made the correction and reposted. Thanks.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[bbaker1987]

Thank you! Problem is I can't get to that link. Is it possible to post the measure(s) or possibly email the file?

[Ashish_Mathur]

You are welcome.  Please find the attached file (below the signature)

[bbaker1987]

I think it's close but the Max Date measure formula takes the max date of whatever dates column is put into it no matter what the corresponding filters are past that. I tried it with two separate date column information and each one always put out the max date of the dates.

[Ashish_Mathur]

Not clear about what you want.  Share the PBI file and show the the problem there clearly.  Also show the expected result.

[bbaker1987]

I expanded the data on the file you sent and added the measures I am currently trying to correct. I can find the date of the max total sum of individual products for a given day. The problem is, when I try to go isolate this specific day's total values and list summed product totals and the overall total, the product values take their individual summed maximum date values (and date) instead of the maximum summed total date and the corresponding product totals for that specific date.

It seems I don't have the right permissions to attach the file and I have no ability to provide a link so please bare with me and forgive the screenshots and text.

I added some more rows to the existing table:

date	prod	qty
27-02-2023	a	15
27-02-2023	b	5
27-02-2023	c	2
27-02-2023	a	15
27-02-2023	b	10
27-02-2023	c	5
28-02-2023	a	15
28-02-2023	b	10
28-02-2023	c	10
28-02-2023	a	10
28-02-2023	b	20
28-02-2023	c	10
01-03-2023	a	5
01-03-2023	a	10
01-03-2023	b	5
01-03-2023	b	5
01-03-2023	c	5
01-03-2023	c	5
02-03-2023	a	10
02-03-2023	b	5
02-03-2023	c	5
03-03-2023	a	5
03-03-2023	b	5
03-03-2023	c	5

My Measure to find the date with the highest total summed Product Value:

THTermMax_Date =
CALCULATE(
MAXX(
TOPN(1,
SUMMARIZE('Calendar','Calendar'[Date],"Max Vol",[Quantity]),
[Max Vol]),
'Calendar'[Date]
),
REMOVEFILTERS('Calendar'[Date])
) 

Measure to display the Quantities from the date found above:

THTermMax_Vol =
CALCULATE(
MAXX (
SUMMARIZE (
ALLSELECTED ( 'Calendar'),
[Date],
"Max", [Quantity] ),
[Max]
),
REMOVEFILTERS('Calendar'[Date]),FILTER('Calendar','Calendar'[Date]=[THTermMax_Date])

)

The date works, but you'll notice the in the Matrix/Table that the values do not correctly add up. This is because "a" total is using its max value from 02/27/2023 instead of using the day's total from 02/28/2023. I am expecting the "a" total to be 25 which is the total from 02/28. This is not isolated to "a" either. If I change the values on different days and make them the maximum for that product, the maximum product total will show regardless of the max total for the entire day.

[Ashish_Mathur]

It is a lot easier to help if i have the file.  Share the download link of the PBI file.  Show the result there clearly.

[bbaker1987]

As mentioned before, I have no ability to share the link per my company, which is regretful. All of the measures shown in the previous post and the additions to the table were added to the file you provided earlier. That's the best I can do given my restrictions.

[bbaker1987]

I'm still working on this with no solution yet. I would be happy to send the file you had previously attached with my updates if you could send an email address. I just have no other way to share the file.

[Ashish_Mathur]

Hi,

The total quantity on Feb 27 is 52 and on Feb 28 is 75.  In the result why are you showing Feb 27 then?