cancel
Showing results for
Did you mean:
Frequent Visitor

Hi Community! I know I'm in the right place to get some help...

I have a table of donors that is connected to a table of pledges, and this one is also connected to a table of gifts. These donors donate monthly.

The relationships are 1 to many in the same order described. One donor can have one or more pledges (commitments of donations) and a pledge has one or more gifts (their donations really made). Pledges have two states: Active (if they are currently donating) or Inactive.

• tblDonors - PK: DONORID
• tblPledges - FK: DONORID PK: PLEDGEID

It happens that some donors cancel their pledges and after some time, the NGO will try to recover him or her. When they are succesfull, this is what we call a "reactivation". We want firstly to know how many were reactivated. Secondly, it would be great (desirable) to know which donor or which pledges were reactivated.

How should we count them? Well... I need to translate in DAX the following logic and look for those

1. donors with more than one pledge (or pledges with the same DONORID)...
2. if after the date that the pledge got 'Inactive' (with LASTMODIFYDATE)
3. there is another one 'Active' pledge with its activation date (ACTIVATIONDATE) greater than LASTMODIFYDATE

Does it sound complicated? What approach would you suggest?

Best,

Gerónimo

1 ACCEPTED SOLUTION
Microsoft

Hi @gtutusaus,

Based on the sample in this thread. I try to reproduce and post the following solutions.

First, my logic is filter all the donors whose status are active, and filter all the donors whose status are inactive in separate tables(Active and Inactive tables). Then get the minimum date for donors, and remove the duplicates in inactives.

Create two table by clicking "New Tables" under modeling on home page based on the requirement above.

`Active = SELECTCOLUMNS(FILTER(Pledges2,Pledges2[PLEDGE STATUS]="Activo"),"HonorId",Pledges2[DONOR ID],"Date",Pledges2[DATE PLEDGE ACTIVATION])`

`Inactive = DISTINCT(SELECTCOLUMNS(FILTER(Pledges2,Pledges2[PLEDGE STATUS]="Inactivo"),"HonorId",MIN(Pledges2[DONOR ID]),"Date",Pledges2[DATE PLEDGE ACTIVATION]))`

Second, create relationship between the two new tables.

Third, create a calculated column using the formula.

`flag = IF( ISBLANK(RELATED(Inactive[Date])),BLANK(),IF(RELATED(Inactive[Date])<Active[Date],"Yes","NO"))`

Finally, create a measure to calculate reactivations, and create a card visual to display it.

`reactive = COUNTROWS(DISTINCT(FILTER(Active,Active[falg]<>BLANK()&&Active[falg]="Yes")))`

Best Regards,
Angelia

7 REPLIES 7
Regular Visitor

Perhaps you could add a calculated to donors with the following formula：

=

VAR table1 =
CALCULATETABLE ( 'Pledges', 'Pledges'[PLEDGE STATUS] = "Inactivo" )
VAR table2 =
CALCULATETABLE ( VALUES ( 'Pledges'[DATE PLEDGE ACTIVATION] ) )
RETURN
COUNTAX (
table1,
COUNTROWS (
FILTER (
table2,
'Pledges'[DATE PLEDGE ACTIVATION]
> EARLIER ( 'Pledges'[DATE PLEDGE ACTIVATION] )
)
)
)

Frequent Visitor

Wow! Didn't know about variables... I added only one filter to table 1 so I skip pledges that were never activated (blank activation dates).

It seems to be working great but how do I propagate it to the pledge level? For example, I would like to know when these reactivations happened by their activation dates.

Many many thanks!
Gerónimo

Microsoft

Hi @gtutusaus,

Based on the sample in this thread. I try to reproduce and post the following solutions.

First, my logic is filter all the donors whose status are active, and filter all the donors whose status are inactive in separate tables(Active and Inactive tables). Then get the minimum date for donors, and remove the duplicates in inactives.

Create two table by clicking "New Tables" under modeling on home page based on the requirement above.

`Active = SELECTCOLUMNS(FILTER(Pledges2,Pledges2[PLEDGE STATUS]="Activo"),"HonorId",Pledges2[DONOR ID],"Date",Pledges2[DATE PLEDGE ACTIVATION])`

`Inactive = DISTINCT(SELECTCOLUMNS(FILTER(Pledges2,Pledges2[PLEDGE STATUS]="Inactivo"),"HonorId",MIN(Pledges2[DONOR ID]),"Date",Pledges2[DATE PLEDGE ACTIVATION]))`

Second, create relationship between the two new tables.

Third, create a calculated column using the formula.

`flag = IF( ISBLANK(RELATED(Inactive[Date])),BLANK(),IF(RELATED(Inactive[Date])<Active[Date],"Yes","NO"))`

Finally, create a measure to calculate reactivations, and create a card visual to display it.

`reactive = COUNTROWS(DISTINCT(FILTER(Active,Active[falg]<>BLANK()&&Active[falg]="Yes")))`

Best Regards,
Angelia

Frequent Visitor

Wow, Angelia! Many many thanks! Let me ask you one more thing... can I create also in Power Pivot the same kind of tables?

Best,

Gerónimo

Microsoft

Hi @gtutusaus,

Yeah, you can also create calculated column and measure in Power Pivot. You can post Power Pivot issue on Power Pivot forum. If my reply is helpful, please mark it as answer.

Thanks,
angelia

Frequent Visitor

@MarcelBeug, can you help me with this? It's the only post I have...

Best,

Gerónimo

Community Champion

Sorry, my DAX knowledge is quite limited.

Specializing in Power Query Formula Language (M)

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.