Return quantity of Item ID for Max Month

[Andrrrejka]

Hi All.

I am looking the way to write DAX neasure to return the quantity of the "Item ID" for the Month with Max quantity. Simplified version of the table is as below. Real table has data with over million lines for 18 months and about 4000 differnt "Item IDs"

[Anonymous]

Hi @Andrrrejka ,

According to your description, I made the following attempts

1. create a new column named " Month" in the table

Month =
MONTH ( Sheet1[Date] )

2. Group tables by "Item ID", then in each group, group tables by month and create a new column to sum the "Quantity"

Table =
SUMMARIZE (
    'Sheet1',
    Sheet1[Month],
    Sheet1[Item ID],
    "total", SUM ( 'Sheet1'[Quantity] )
)

But unfortunately, I don't understand what you mean by this sentence ("Max Month = return value from Table of sum( Quantity) for the month with Max sum(Quantity) for each individual "Item ID" "). Could you explain it further ?

If I misunderstand something above, please feel free to let me know.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Andrrrejka]

Hello, 

I have a separate table for Dates which is fine. 

Based on your calculation I need to create a measure to add it to another measure which would return value with quantity for a Max month. 

E.g. from  this table:

for Item ID 1 it it should return value of 13 and for Item ID 2 - 26 which are Max Values for the month.

[Anonymous · Accepted solution]

Hi @Andrrrejka ,

You can follow the below steps:

1.Create a new table by click “New table” under “Modeling” , then create a measure:

Table 2 =
SUMMARIZE ( 'Table', 'Table'[Item ID], "MaxQuantity", MAX ( 'Table'[total] ) )

2.In report page, add a table visual, then add the data fields, you can get below visualization

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Andrrrejka ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[Andrrrejka]

Ok, 

I have Data table:

Starting from 01/01/2021 until now.

Each line in Data Table represents a line from invoice where "Item ID" is a product invoiced. Thus, it could be several lines with same "Item ID" per month.

I would like to write the measure to return value for the month with max quantity sold for each "Item ID"

Max Month = return value from Table of sum( Quantity) for the month with Max sum(Quantity) for each individual "Item ID"