Forum Discussion

okiedokie2017's avatar
okiedokie2017
Icon for Helper I rankHelper I
8 years ago
Solved

Maximum count every single month for each unique customer

Hello, 

 

I have been stuck on this issue for ages and would like someone to take a look at it. 

 

Basically, I have a table like this: 

 

Configured Original Timestampusers.customers 
113/02/2018 20:25A
414/02/2018 20:25A
615/02/2018 20:25B
1216/02/2018 20:25A
517/02/2018 20:25B
718/02/2018 20:25E
2519/02/2018 20:25C

 

What I am trying to achieve is this: 

User.customers Max Configured per MonthDate 
A1216/02/2018
B615/02/2018
C2519/02/2018

 

 

At the moment, the dataset is from Jan 2017 till Feb 2018 so I would like to get the maximum of "configured" number every month for each unique customers. 

 

This is what  I have so far but it is not working. 

 

 

Table =
VAR tblMonths =
SUMMARIZE(
ADDCOLUMNS(
CALENDAR("2016-11-01","2018-03-01"),
"Month",DATE(YEAR([original_timestamp]),MONTH([original_timestamp]),1)
),[Month])

RETURN
ADDCOLUMNS(
GENERATE(
VALUES(system_monitored[users.customer]),
tblMonths
)
,"Order Value" ,
CALCULATE(
MAX(system_monitored[configured]),
FILTER(system_monitored,
system_monitored[users.customer] = EARLIER(system_monitored[users.customer]) && system_monitored[original_timestamp] = EARLIER([Month])
)
))

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi okiedokie2017,

     

    If I understand you correctly, you should be able to follow steps below to get the expected result in your scenario.

     

    1. Use the formula below to add a YearMonth column in table 'system_monitored'.

    YearMonth = YEAR(system_monitored[Original Timestamp])*100+MONTH(system_monitored[Original Timestamp])

    2. Then you should be able to use the formula below to create the expected table. 

    Table = 
SUMMARIZE (
    system_monitored,
    system_monitored[YearMonth],
    system_monitored[users.customers ],
    "Max Configured per Month", CALCULATE ( MAX ( system_monitored[Configured] ) ),
    "Date",
    VAR abc =
        CALCULATE ( MAX ( system_monitored[Configured] ) )
    RETURN
        CALCULATE (
            MAX ( system_monitored[Original Timestamp] ),
            system_monitored[Configured] = abc
        )
)

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi okiedokie2017,

     

    If I understand you correctly, you should be able to follow steps below to get the expected result in your scenario.

     

    1. Use the formula below to add a YearMonth column in table 'system_monitored'.

    YearMonth = YEAR(system_monitored[Original Timestamp])*100+MONTH(system_monitored[Original Timestamp])

    2. Then you should be able to use the formula below to create the expected table. 

    Table = 
SUMMARIZE (
    system_monitored,
    system_monitored[YearMonth],
    system_monitored[users.customers ],
    "Max Configured per Month", CALCULATE ( MAX ( system_monitored[Configured] ) ),
    "Date",
    VAR abc =
        CALCULATE ( MAX ( system_monitored[Configured] ) )
    RETURN
        CALCULATE (
            MAX ( system_monitored[Original Timestamp] ),
            system_monitored[Configured] = abc
        )
)

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards