DAX: how to select max per date & per month

Question

I have a table that keeps Date and Quantity, I need to define MAX Quantity each Month at Date level.

Here is an example:

Date           Quantity Max Quantity per Month
01.02.19    20 351  40 952
02.02.19    14 176  40 952
03.02.19    25 218  40 952
23.02.19    13 244  40 952
24.02.19    14 021  40 952
25.02.19    33 173  40 952
26.02.19    21 233  40 952
01.04.19    11 855  40 952
24.04.19    19 113  40 952
25.04.19    40 952  40 952
26.04.19    37 460  40 952

Here MAX Qty in February is 33 173, in April 40 952 But my current measure displays a total max of 40 952

Here is DAX used:

Max Quantity per Month = 
CALCULATE(MAXX (
    SUMMARIZE (
        'Table1',
        'Date'[Year Month],
        'Table1'[Date],
        "Qty", [Quantity]
    ),
    [MAX_Qty]
), ALLEXCEPT('Table1', 'Table1'[Date], 'Date'[Year Month]))

What correct DAX should be to display different value per month?

Aldert Aldert · Accepted Answer · 2019-05-31T11:53:56

You can use:

Max Quantity per Month =
CALCULATE (
    MAX ( Table1[Quantity] ),
    FILTER (
        Table1,
        MONTH ( Table1[Date] ) = MONTH ( EARLIER ( Table1[Date] ) )
            && YEAR ( Table1[Date] ) = YEAR ( EARLIER ( Table1[Date] ) )
    )
)

DAX: how to select max per date & per month

1 Answers