Flitering customer numbers across 2 tables

[Anonymous]

Hi,

I was hoping to get some assistance.  I have 2 tables; customer table and a donations table.  In the cutomer table i have a list of customer numbers, the date they commenced and ended and which campaign they were recruited to. In the donations tables I have a list of the same customer numbers and all the donations amounts and dates that they made a donations.  In order to linked these two tables I have created a reference table which contains all unique customer numbers.

From the customer table I have created cohort by month and year with the following formula to get how many have started each month. Cohort = EOMONTH('Norge faddere DM'[DateStart],-1)+1 And have included the drop out as well and then of course the attrition as below.

From the donations table I would like work out the income generated by cohort for different years, the amount of donations, and average donation etc to be filtered by the cohorts and their specific recruitment numbers (customers)

For number of donations i have written the following formula:

# Donations 2019 = CALCULATE(
COUNT('Jan 19 xls'[Beløp]) ,
FILTER('Norge faddere DM', 'Norge faddere DM'[Cohort].[Date])
)

However this doesn't seem to work as you can see below. ( I have filtered on same specific campaigns, which you can see in the table underneath the first one).

Total income should be 21, 914, 939 as showed in the donations table. There might be some variance but not this large.

I really welcome your assistance. Thank you,

Maria

[v-cherch-msft]

Hi @Anonymous

I cannot fully understand it.You want to get the number of donations for each Month,right?Could you explain more about your expected output?Please share the .pbix file for us to check the relationship and data structure.You can upload it to OneDrive and post the link here or send me via private message. Do mask sensitive data before uploading.

Regards,

Cherie

[Anonymous]

Here is how I have set up the relationships.  Kundenummer means customer number

[v-cherch-msft]

Hi @Anonymous

It's hard for us to provide an accurate solution without sample data.Could you share a simplified sample to point out your issue?Based on my guessing, you may try CALCULATETABLE function.Please check this article for reference.

Regards,

Cherie

[Anonymous]

Hi,

I will send this to you soon. 

I tried the following formula as you suggested and it seemed to work,  #donations amount increase with some expected deviation. However it did not filter on each cohort and total those up.  Perhaps the all function is not correct here?  I tried allselected and that did not work either.

# Donations 2018 = CALCULATE(
COUNT('jan 17 - dec 18'[Beløp]) ,
FILTER(
ALL('Norge faddere DM'),
('Norge faddere DM'[Cohort].[Date])
)
)

[Anonymous]

Hi,

My customer table looks like this:

I'm put xxxx where the customer numbers would be.  The avslutningsdato = end date and Startdate = start date.  I filter on the l3rd and 2nd last columns based on the campaign activity they have been recruited from.

I've done the same here with the crosses for kundenummer.  This lists the customer number multiple times against the betalingsdato = payment date which can be seen throughout jan 17 - des 18.  I would like to filter the amounts of payments against the cohorts column I have made in the above customer table.

Does this help at all?

Thank you,

Maria

[v-cherch-msft]

Hi @Anonymous

It seems you may try USERELATIONSHIP Function.Please refer to below post's attachment for more details.

https://community.powerbi.com/t5/Desktop/Single-Monthly-trend-by-using-2-Timestamp-and-2-ID-values-from/m-p/622160#M297021

https://community.powerbi.com/t5/Desktop/Count-Create-and-End-Dates-in-columns/m-p/621462#M296645

Regards,

Cherie

[Anonymous]

Hi,

Thank you,

I tried that but it didn't work.  I think that it is because I have a reference number containing all the customer numbers, so the two table don't directly link up. I get this error mesasge - USERELATIONSHIP function can only use the two columns references participating in relationship.

What do you think ?

Maria 🙂

[Anonymous]

Hi,

Thank you for trying to help me.

I would try to track donations by cohort (in the first table) instead of by campaigns which is seen in the second table. However my I can't seem to filter on the customer numbers by cohort in the second table to match roughly the amount of donations given by each cohort.  

Let me know if this doesn't make sense and I'll share more.

Thank you,

Maria