Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good morning,
I have 2 tables, 1 is a calendar table, the other one is a table showing new members per location with their registered date and cancellation date, see below:
UniqueID | Registered date | Cancelled date | Location |
6453691590 | 12-Dec-18 | 06-May-19 | Cambridge |
7143468592 | 14-Feb-19 | London | |
7098239194 | 22-Jun-16 | 05-Jul-18 | Oxford |
7107042262 | 26-Jul-18 | Cambridge | |
7139165726 | 09-Mar-16 | 10-Mar-19 | Cambridge |
7143543489 | 10-Oct-18 | Oxford | |
7190306099 | 29-Aug-18 | London | |
6420897211 | 28-Jul-10 | 08-Sep-17 | Oxford |
6473635939 | 08-Feb-17 | Oxford |
Please note that this is just a sample of the table, the orignal has thousands of rows. The other table is a simple calendar table with Date and Month Year columns.
I need to create a graph / table showing a cumulation of all the current members per month per location.
Basically the calculation should cumulate all the new registered, remove the one that have cancelled their membership.
The idea is to show how many members are registered per location every month.
This below, are the calculations I have tried but even though the figures are not far off what I expect to see, they are still different and I don't know why:
1. Number of members: [Cumulation new registered] - [Cumulation of cancelled]
2. Cumulative new registered:
=CALCULATE(
DISTINCTCOUNT('ShowMembers'[UniqueID]),
USERELATIONSHIP('Calendar'[Date],'ShowMembers'[Registered date]),
DATESBETWEEN('Calendar'[Date],DATE(2000,01,01),MAX('Calendar'[Date])),
)
3. Cumulative of cancelled:
=CALCULATE(
DISTINCTCOUNT('ShowMembers'[UniqueID]),
USERELATIONSHIP('Calendar'[Date],'ShowMembers'[Cancelled date]),
DATESBETWEEN('Calendar'[Date],DATE(2000,01,01),MAX('Calendar'[Date])),
)
As I mentionned above, the results are closed to what we should see but still different, could someone please help me with this?
Thank you in advance for your help.
Hi,
Thakns for your reply
The results should look like this:
Numbers of Members per location | ||||||
Jan-18 | Feb-18 | Mar-18 | Apr-18 | May-18 | … | |
Cambridge | 123 | 120 | 126 | 128 | 129 | … |
London | 122 | 127 | 127 | 124 | 120 | … |
Oxford | 106 | 104 | 105 | 103 | 104 | … |
Cumulative new registered is counting all the members that have been registered so far.
Cumulative of cancelled will count all the members that have cancelled their membership.
Hope this helps.
Hi Darlove,
Thanks for your patience,
so, as per you example, for a selected period of Jan-2018, the "cumulative new registered" is count UniqueID where "Registered Date" <=31-Jan-2018 (as it needs to also include all the new members that have registered in January)
And the "Cumulative of Cancelled" for the same period is the count of UniqueID where "Cancelled date" <= 31-Jan-2018
If the code I've given you is correct, please mark the post as THE answer.
Thanks.
Best
Darek
-- Of course, 'Calendar' must be linked to -- Registered Date and Cancelled Date, one -- relationship being active (probably the -- one to with Registered Date). [Registered To Date] = var __lastVisibleDate = MAX ( Dates[Date] ) var __registeredToDate = CALCULATE( DISTINCTCOUNT( ShowMembers[UniqueID] ), 'Calendar'[Date] <= __lastVisibleDate, USERELATIONSHIP( 'Calendar'[Date], ShowMembers[Registered Date] ) ) return __registeredToDate [Cancelled To Date] =
var __lastVisibleDate = MAX ( Dates[Date] )
var __cancelledToDate =
CALCULATE(
DISTINCTCOUNT( ShowMembers[UniqueID] ),
'Calendar'[Date] <= __lastVisibleDate,
'Calendar'[Date] <> BLANK(),
USERELATIONSHIP(
'Calendar'[Date],
ShowMembers[Cancelled Date]
)
)
return
__cancelledToDate
Calendar must be marked as a Date table in the model.
Best
Darek
Hi Darek,
I have tested the calculation, we are getting closer 🙂
This is what the results show:
Mar-19 | Apr-19 | May-19 | Jun-19 | |
Cambridge | 120 | 126 | 128 | 129 |
London | 127 | 127 | 124 | 120 |
Oxford | 104 | 105 | 103 | 104 |
When comparing with the raw data it looks like for example for Jun 19, the members registered in June and the ones that have cancelled in June are missing.
The calculation shows a total of 353 members in June 19, where the raw data shows that we should have 363
I have noted that 4 members have registered in June and 6 have cancelled, which explains the 10 missing.
Any idea how the calculation could include those?
Sorry but it's your responsibility to define what the meaning is of the numbers you want to calculate. I only implement the algorithm you give me. If the definition is not what you really meant, you have to change it and re-implement.
Sorry.
Best
Darek
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |