Highest Monthly Total in A Given Time Period - Measure

[Anonymous]

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!

[Anonymous]

Thank you both very much for your help! @CNENFRNL @Jos_Woolley 

[CNENFRNL · Accepted solution]

[Jos_Woolley]

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