Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Assign value based on key

Hi,

 

Complete newbie in Power BI here, so please bear with me. Many thanks in advance!!!

 

What I am trying to achieve? determine the donor status based on 2 criterias: A donor is inactive

  1. Date rule: if they have didn't donate in the last 2 years (2014 and 2013), and
  2. Amount rule: if the donation is lower than 5 USD.

To note:

  • A donor can have multiple donations on the same date. So I need to apply the amount rule (> 5 USD) on the total donation for a given day

How I proceeded?

  • I created a new table to group donorID and donation_date, and summed the donations.
  • I then proceeded with an IF statement to identify Active/Inactive donors

My issue:

Considering that a donor can have donated on multiple dates, for a given donor I end up with a given donor being active AND inactive (see screenshot below).

My question:

How can I apply the donor status for all the rows of a given donor?

 

 

 

 

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Anonymous 

    Here are the steps you can refer to :

    (1)This is my test data :

    (2)You can create a calculated column :

    Donor_Status = var _current_id='amount_donated_last_date'[donorID]
var _current_date = 'amount_donated_last_date'[date]
var _sum =SUMX(FILTER('amount_donated_last_date','amount_donated_last_date'[donorID]=_current_id && amount_donated_last_date[date] = _current_date),[Total])

return
IF(YEAR('amount_donated_last_date'[date]) >2012 && _sum >5 , "Active","Inactive")

    (3)Then we can meet your need , the result is as follows:

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

     

    Best Regards,

    Aniya Zhang

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

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    Try to add the new column with the code below:

     

    Donor_Status=
    VAR LastYear = 
    CALCULATE(
         YEAR(MAX(amount_donated_last_date[date])),
        ALLEXCEPT(amount_donated_last_date, amount_donated_last_date[donorID])
    )
    VAR TotalSum =
    CALCULATE(
         SUM(amount_donated_last_date[Total]),
        ALLEXCEPT(amount_donated_last_date, amount_donated_last_date[donorID])
    )
    RETURN 
    IF ( LastYear >=2012  && TotalSum>5, "Active", "Inactive" )
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks a lot for your contribution!

       

      It doesn't seem to be working unfornutaly. For donorID 100005 yes, but not for 100003 as you can see on the screenshots below. 100003 should be inactive (all donations in 2013 are below 5 USD).

       

       

       

       

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Community Support
        3 years ago

        Hi , Anonymous 

        Here are the steps you can refer to :

        (1)This is my test data :

        (2)You can create a calculated column :

        Donor_Status = var _current_id='amount_donated_last_date'[donorID]
var _current_date = 'amount_donated_last_date'[date]
var _sum =SUMX(FILTER('amount_donated_last_date','amount_donated_last_date'[donorID]=_current_id && amount_donated_last_date[date] = _current_date),[Total])

return
IF(YEAR('amount_donated_last_date'[date]) >2012 && _sum >5 , "Active","Inactive")

        (3)Then we can meet your need , the result is as follows:

        If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

         

        Best Regards,

        Aniya Zhang

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