Max quantity by month

[ABLRP]

Hello,

I need to retrieve the MAX per PN M by month. I currently have the below measure, but it is not retriving the correct value. It is retrieving the MAX value per date. The desired output is 81 and not 80.

MAX DEMAND (last year) =

CALCULATE(MAX('INVENTORY HISTORY'[QTY]),'INVENTORY HISTORY'[TRANSACTION_TYPE]="ISSUED",'INVENTORY HISTORY'[TRANSACTION CREATED_DATE]>=(DATE(YEAR(NOW())-1,MONTH(NOW()),DAY(NOW()))))

Could you please assist me?

Thank you!

Barbara

[v-shex-msft · Accepted solution]

Hi @ABLRP,

I modify the formula to use both year and month fields as conditions(based on current row date), you can try it if helps:

MAX DEMAND =
VAR currDate =
    MAX ( 'INVENTORY HISTORY'[TRANSACTION CREATED_DATE] )
RETURN
    CALCULATE (
        MAX ( 'INVENTORY HISTORY'[QTY] ),
        FILTER (
            ALLSELECTED ( 'INVENTORY HISTORY' ),
            'INVENTORY HISTORY'[TRANSACTION_TYPE] = "ISSUED"
                && YEAR ( 'INVENTORY HISTORY'[TRANSACTION CREATED_DATE] )
                    = YEAR ( currDate ) - 1
                && MONTH ( 'INVENTORY HISTORY'[TRANSACTION CREATED_DATE] ) = MONTH ( currDate )
        )
    )

Regards,

Xiaoxin Sheng

[amitchandak]

@ABLRP , Create a measure like below, Use month in place of date if need, create a column for month

maxx(summzarize(Table, Table[PN I], Table[Transaction_created_date], "_1", Sum(Table[Qty])), [_1])