Calculate total previous year points earned based on decimal customer length and customer type?

[alya1]

Hello All,

I have a table like below

ID	Customer Length (yrs)	Customer Type	Benefit points per day (calculated column based on customer length and type)
111	3.1	A	10
222	8	B	20
333	5.2	C	30

The benefit points per day is calculated using below logic
Customer type A, less than 3 yrs = 5, 3+ yrs = 10
Customer type B, less than 3 yrs = 10, 3+ yrs = 20
Customer type C, less than 1 yr = 10, 1-5 yrs = 20, 5-10 yrs = 30, 10+ yrs = 40

I only have data as of today. 
Is it possible to calculate the total points earned per customer throughout the entire last year (total 365 days) using the information I have above in PowerBI? 
The current rate * 365 would not be accurate as that some customers just pushed past a new benefit points tier like 111 and 333.
See below for goal results column and reasoning: 

ID	Calc	Results	Notes
111	0.1*365*10+0.9*365*5	2007.5	Since 111 is at 3.1 yrs, only 0.1 of the year is at 10 pts/day and 0.9 is at the previous tier of 5 pts/day
222	20*365	7300	Since 222 is at 8 yrs and more than 1 yr from cut off of 3 yrs, it's just * 365
333	0.2*365*30+0.8*365*20	8030	Since 333 is at 5.2 yrs, only 0.2 of the year is at 30 pts/day and 0.8 is at the previous tier of 20 pts/day

Thank you!

[Anonymous · Accepted solution]

Hi, @alya1

Thanks for ryan_mayu's concern about this issue.

I am glad to help you.

According to your description, you can refer to my DAX formula New Column:

TotalPointsLastYear =
VAR CustomerLength = [Length (yrs)]
VAR CustomerType = [Customer Type]
VAR PointsPerDay =
    SWITCH (
        TRUE (),
        CustomerType = "A"
            && CustomerLength < 3, 5,
        CustomerType = "A"
            && CustomerLength >= 3, 10,
        CustomerType = "B"
            && CustomerLength < 3, 10,
        CustomerType = "B"
            && CustomerLength >= 3, 20,
        CustomerType = "C"
            && CustomerLength < 1, 10,
        CustomerType = "C"
            && CustomerLength >= 1
            && CustomerLength < 5, 20,
        CustomerType = "C"
            && CustomerLength >= 5
            && CustomerLength < 10, 30,
        CustomerType = "C"
            && CustomerLength >= 10, 40
    )
VAR PreviousTierPoints =
    SWITCH (
        TRUE (),
        CustomerType = "A"
            && CustomerLength < 3, 5,
        CustomerType = "A"
            && CustomerLength >= 3, 5,
        CustomerType = "B"
            && CustomerLength < 3, 10,
        CustomerType = "B"
            && CustomerLength >= 3, 10,
        CustomerType = "C"
            && CustomerLength < 1, 10,
        CustomerType = "C"
            && CustomerLength >= 1
            && CustomerLength < 5, 20,
        CustomerType = "C"
            && CustomerLength >= 5
            && CustomerLength < 10, 20,
        CustomerType = "C"
            && CustomerLength >= 10, 30
    )
VAR FractionNewTier =
    CustomerLength - FLOOR ( CustomerLength, 1 )
VAR FractionOldTier = 1 - FractionNewTier
RETURN
    ( FractionNewTier * 365 * PointsPerDay ) + ( FractionOldTier * 365 * PreviousTierPoints )

Here are my test results:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alya1]

thank you fenling!!!

[ryan_mayu]

@alya1 

could you pls provide the expected output?

[alya1]

Hi Ryan,
Yes please see below Results column! I added calc and notes for background thank you!

ID	Calc	Results	Notes
111	0.1*365*10+0.9*365*5	2007.5	Since 111 is at 3.1 yrs, only 0.1 of the year is at 10 pts/day and 0.9 is at the previous tier of 5 pts/day
222	20*365	7300	Since 222 is at 8 yrs and more than 1 yr from cut off of 3 yrs, it's just * 365
333	0.2*365*30+0.8*365*20	8030	Since 333 is at 5.2 yrs, only 0.2 of the year is at 30 pts/day and 0.8 is at the previous tier of 20 pts/day