cancel
Showing results for
Did you mean:
Frequent Visitor

Cumulative calculation issue

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?

8 REPLIES 8
Anonymous
Not applicable
Well, until you show what the correct results should be... nobody will be able to help you. Please state in words, but precisely like you do in mathematics, what it means "Cumulative new registered" and "Cumulative cancelled" for any selected period of time. Thanks.
Frequent Visitor

Hi,

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.

Anonymous
Not applicable
"Cumulative new registered is counting all the members that have been registered so far." Well, that's not particularly clear. Say you have selected a period of Jan-2018. Does "Cumulative new registered" mean that you count UniqueID's where "Registered Date" is < 1-Jan-2018? Or "Registered Date" is <= 1-Jan-2018? Or something else entirely? The other one is also not clear enough. Should "Cumulative cancelled" be the number of UniqueID's where Cancellation Date <= 31-Jan-2018? Or something different? Please state the conditions clearly using the entities in your table.

Thanks.

Best
D.
Frequent Visitor

Hi Darlove,

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

Anonymous
Not applicable

If the code I've given you is correct, please mark the post as THE answer.

Thanks.

Best

Darek

Anonymous
Not applicable
```-- 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

Frequent Visitor

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?

Anonymous
Not applicable

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

Announcements