How to select a Max Value in a given month for each customer?

[ssr80]

Hi Everyone,

Can someone help me with the following scenario.

I have one table for orders and one table with customer details

i need to create a table with CustomerID, Month and Highest order value in that paticular month.

Result should look like below:

Any help would be appreciated. 

regards

SS

[ssr80 · Accepted solution]

Hi Phil,

Great job, That's awesome.

it works as per my requirement.

Thanks for all your efforts.

Regards

SS

[Phil_Seamark]

Hi @ssr80

This Calculated Table looks close.  Just replace where I have Table2 with the name of your table.

Table = 
VAR tblMonths = 
    SUMMARIZE(
        ADDCOLUMNS(
            CALENDAR("2017-11-01","2018-01-01"),
            "Month",DATE(YEAR([Date]),MONTH([Date]),1)
            ),[Month])
            
RETURN    
    ADDCOLUMNS(
        GENERATE(
            VALUES('Table2'[Customer]),
            tblMonths
            )
            ,"Order Value" , 
                CALCULATE(
                    MAX('Table2'[Order Value]),
                    FILTER('Table2',
                    'Table2'[Customer] = EARLIER('Table2'[Customer]) && 'Table2'[Date] = EARLIER([Month])
                    )
                    ))

[ssr80]

Hi Phil,

Thanks for the reply. that was great, appreciate your effort.

there is a small error in the results.

it's giving me the right value only when the order is placed on 01st of the month.

But, if the order is placed on other days of the month. it's returning null.

sorry, may be my question wasn't clear.

the order date is actually in the format of date and time.

Regards

SS

[Phil_Seamark]

HI @ssr80

Please try this slight modification.  I have marked in bold (red) the change.

Table = 
VAR tblMonths = 
    SUMMARIZE(
        ADDCOLUMNS(
            CALENDAR("2017-11-01","2018-01-01"),
            "Month",DATE(YEAR([Date]),MONTH([Date]),1)
            ),[Month])
            
RETURN    
    ADDCOLUMNS(
        GENERATE(
            VALUES('Table2'[Customer]),
            tblMonths
            )
            ,"Order Value" , 
                CALCULATE(
                    MAX('Table2'[Order Value]),
                    FILTER('Table2',
                    'Table2'[Customer] = EARLIER('Table2'[Customer]) && DATE(YEAR('Table2'[Date]),MONTH('Table2'[Date]),1) = EARLIER([Month])
                    )
                    ))

[ssr80 · Accepted solution]

Hi Phil,

Great job, That's awesome.

it works as per my requirement.

Thanks for all your efforts.

Regards

SS

[v-chuncz-msft · Accepted solution]

@ssr80,

By the way, you may help accept solution. Your contribution is highly appreciated.

[ssr80]

Hi Phil,

further to this topic.

orders are recorded in dd/mm/yyyy tt:hh:ss format.

But, i need to report monthwise highest order value for each customer.

Thank you.

Regards

SS

[Ashish_Mathur]

Hi @ssr80,

Share your two datasets (that can then be pasted in MS Excel)

[ssr80]

Hi,

Here is the two datasets i have.

1 table with the list of orders

Ord   CustomerID    Date                        OrderType

1        10530           7/02/2018 14:17      -1
2        10520           5/02/2018 15:58        3
3        10520           5/02/2018 14:07       -1
4         10520          5/02/2018 13:37       -1
5        10520          5/02/2018 13:29       -1
6        10517          5/02/2018 15:24       -1
7        10517          6/02/2018 3:00           3
8        10512         1/02/2018 2:09           3
9        10496         1/02/2018 11:31         3
10      10496         1/02/2018 11:27        -1

2nd table 

 CustomerID     MonthYear       
10532               Feb-18 
10531               Feb-18 
10530               Feb-18 
10528               Feb-18 
10527               Feb-18 
10526               Feb-18 
10520               Feb-18 
10517               Feb-18 
10514                Feb-18 
10512               Feb-18            
10499               Feb-18 
10496               Feb-18     

the result should have customerID, MonthYear, MaxOrderType for the month

i am getting the following result when i try the solution from Phil.

as you can see from the orders table

customerID 10520 should have value 3

customerID 10517 should have value 3

and so on.

 CustomerID     MonthYear       MaxOrdertype
10532               Feb-18
10531               Feb-18
10530               Feb-18
10528               Feb-18
10527               Feb-18
10526               Feb-18
10520               Feb-18
10517               Feb-18
10514                Feb-18
10512               Feb-18             3
10499               Feb-18
10496               Feb-18             3

Any help would be appreciated. 

Thanks.

Regards

SS