Forum Discussion
Problems creating a particular matrix (multiple dates of reference)
Hello everyone.
I have some problems creating a particular matrix (and other visual objects related to this).
I have a table [User], with columns [open_id] (the ID of the user), [subscription_date] (date of subscription), and [unsubscription_date].
The unsubscription date is blank if the user hasn't unsubscribed.
What I'd like to achieve is a matrix having years as column and months as rows, and I'd like to display the number of subscriptions and unsubscriptions.
The formula are easy: COUNT(subscription_date) and COUNT(unsubscription_date).
The real problems I'm facing are the column and rows.
If I use the YEAR(subscribe_date) as columns and MONTH(subscribe_date) as rows, I'll get the following problem.
Let's say I have the following entries (date is DD/MM/YYYY):
For May 2015 the matrix will show 5 subscriptions (correct, ID 1,2,4,5,6) and 2 unsubscriptions (ID 1,2 are correct, but ID 3 is omitted) because it'll show only the entries related to users who subscribed at May 2015.
What I'd like my matrix to show is 5 subscriptions (ID 1,2,4,5,6) and 3 unsubscriptions (ID 1,2,3) for May 2015.
What would be the best way to achieve this?
Thanks to anyone who may help me.
I managed it somehow: I created a master calendar, linked it by subscription and unsubscription. then used these formulas:
NumberOfSubscriptions = COUNTROWS(RELATEDTABLE('user_info'))
NumberOfUnsubscriptions = CALCULATE(COUNTROWS(RELATEDTABLE('user_info')); USERELATIONSHIP('Master Calendar'[MasterDate]; 'user'[UnsubscribeDate]))
so now I have the number of subscriptions and unsubscriptions for each date.
But I'd like to avoid to use the master calendar for other purposed.
Can someone suggest me another way to do it?
2 Replies
I managed it somehow: I created a master calendar, linked it by subscription and unsubscription. then used these formulas:
NumberOfSubscriptions = COUNTROWS(RELATEDTABLE('user_info'))
NumberOfUnsubscriptions = CALCULATE(COUNTROWS(RELATEDTABLE('user_info')); USERELATIONSHIP('Master Calendar'[MasterDate]; 'user'[UnsubscribeDate]))
so now I have the number of subscriptions and unsubscriptions for each date.
But I'd like to avoid to use the master calendar for other purposed.
Can someone suggest me another way to do it?
