Assign value based on key

[Anonymous]

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?

[FreemanZ]

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]

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).

[FreemanZ]

aha, you wanna check the max donation, try this:

Donor_Status=

VAR LastYear =

CALCULATE(

     YEAR(MAX(amount_donated_last_date[date])),

    ALLEXCEPT(amount_donated_last_date, amount_donated_last_date[donorID])

)

VAR MaxAmount=

CALCULATE(

     MAX(amount_donated_last_date[Total]),

    ALLEXCEPT(amount_donated_last_date, amount_donated_last_date[donorID])

)

RETURN

IF ( LastYear >=2012  && TotalSum>5, "Active", "Inactive" )

[v-yueyunzh-msft · Accepted solution]

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