users rating excluding users registered less then 3 months ago

[kristina-brooly]

Hello, please help me to calculate users rating

«users rating» = «sales rank» * 0.75 + «bonus rank» * 0.25

«sales rank» and  «bonus rank» — ranging sales growth in % (last month to a month before that (2 months ago)

Its August now so we need to calculate % growth June/July.

User is included to this calculation if he's been registered more then 3 months ago (current month excluded).

Tables

Users

• user_id
• name 
• registration_date

Sales

• month_code – year and month in YYYYMM format
• user_id 
•  sale  
•  bonus 

Need to make a table like this: 

User	Sales	Bonus	Average rating
Name	1000000	2000000	1,3

[kristina-brooly]

please help)))

[Anonymous · Accepted solution]

Hi @kristina-brooly ,

Here I create a sample to have a test.

User:

Sales:

Measure:

Average Rating = 
VAR _LASTMONTH =
    EOMONTH ( TODAY (), -1 )
VAR _2MONTHAGO =
    EOMONTH ( TODAY (), -2 )
VAR _Salesrank =
    CALCULATE (
        DIVIDE (
            CALCULATE (
                SUM ( Sales[sale] ),
                FILTER (
                    Sales,
                    Sales[month_code]
                        = YEAR ( _LASTMONTH ) * 100
                            + MONTH ( _LASTMONTH )
                )
            ),
            CALCULATE (
                SUM ( Sales[sale] ),
                FILTER (
                    Sales,
                    Sales[month_code]
                        = YEAR ( _2MONTHAGO ) * 100
                            + MONTH ( _2MONTHAGO )
                )
            )
        ),
        FILTER ( User, User[registration_date] <= EOMONTH ( TODAY (), -4 ) + 1 )
    )
VAR _Bonusrank =
    CALCULATE (
        DIVIDE (
            CALCULATE (
                SUM ( Sales[bonus] ),
                FILTER (
                    Sales,
                    Sales[month_code]
                        = YEAR ( _LASTMONTH ) * 100
                            + MONTH ( _LASTMONTH )
                )
            ),
            CALCULATE (
                SUM ( Sales[bonus] ),
                FILTER (
                    Sales,
                    Sales[month_code]
                        = YEAR ( _2MONTHAGO ) * 100
                            + MONTH ( _2MONTHAGO )
                )
            )
        ),
        FILTER ( User, User[registration_date] <= EOMONTH ( TODAY (), -4 ) + 1 )
    )
RETURN
    _Salesrank * 0.75 + _Bonusrank * 0.25

Filter = 
IF(MAX(User[registration_date])<=EOMONTH(TODAY(),-4)+1,1,0)

Add [Filter] measure into visual level filter and set it to show items when value =1. Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.