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 haved tried the following formula 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])
)
)
My customer table looks like this:
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.
I really hope someone can help me with it.
Thank you!
Maria