Forum Discussion

alya1's avatar
alya1
Icon for Helper V rankHelper V
2 years ago
Solved

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

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)   
1113.1A10
2228B20
3335.2C30


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: 

IDCalcResultsNotes
111  0.1*365*10+0.9*365*52007.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
22220*3657300Since 222 is at 8 yrs and more than 1 yr from cut off of 3 yrs, it's just * 365
3330.2*365*30+0.8*365*20  8030Since 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!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      alya1
      Icon for Helper V rankHelper V
      2 years ago

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

      ID   Calc                               Results  Notes    
      1110.1*365*10+0.9*365*5   2007.5Since 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
      22220*3657300Since 222 is at 8 yrs and more than 1 yr from cut off of 3 yrs, it's just * 365
      3330.2*365*30+0.8*365*20  8030Since 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