topic Re: Highest Monthly Total in A Given Time Period - Measure in DAX Commands and Tips

Highest Monthly Total in A Given Time Period - Measure

Anonymous — Sun, 15 Aug 2021 18:22:42 GMT

Hello,

I am trying to create a measure which can show the highest monthly total sales in a given time period. See the sales table below:

Date	Sales
7/2/2020	$ 100
7/2/2020	$ 200
7/28/2020	$ 500
8/4/2020	$ 300
9/5/2020	$ 200
7/2/2021	$ 100
7/2/2021	$ 100
8/4/2021	$ 900
9/5/2021	$ 200

There is another date table that I want to filter on. When I filter year 2020, the measure should show $800 (July 2021 total sales amount); when I filter 2021, the measure should show $900 (August 2021 total sales amount); when I don't filter anything, it should show $900 (August 2021 total sales amount); when I select a sepecific month, it should show the monthly total sales amount of the month selected - e.g. if I select Sept 2021, it should show $200.

Is there a way to create this measure without creating a new table?

Thank you very much for your help!

Re: Highest Monthly Total in A Given Time Period - Measure

Jos_Woolley — Sun, 15 Aug 2021 19:56:18 GMT

Hi,

Assuming your Sales table is related appropriately to a Date table named Dates:

Max Monthly Sales = VAR MyTable = SUMMARIZE ( Sales, Dates[Date].[Year], Dates[Date].[Month], "Monthly Sales", SUM ( Sales[Sales] ) ) RETURN MAXX ( MyTable, [Monthly Sales] )

Regards

Re: Highest Monthly Total in A Given Time Period - Measure

CNENFRNL — Sun, 15 Aug 2021 21:31:47 GMT

Re: Highest Monthly Total in A Given Time Period - Measure

Anonymous — Sun, 15 Aug 2021 22:50:02 GMT

Thank you both very much for your help! CNENFRNL Jos_Woolley