Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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:

 

UniqueIDRegistered dateCancelled dateLocation
645369159012-Dec-1806-May-19Cambridge
714346859214-Feb-19 London
709823919422-Jun-1605-Jul-18Oxford
710704226226-Jul-18 Cambridge
713916572609-Mar-1610-Mar-19Cambridge
714354348910-Oct-18 Oxford
719030609929-Aug-18 London
642089721128-Jul-1008-Sep-17Oxford
647363593908-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.

 

 

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    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.
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

      Thakns for your reply

      The results should look like this:

      Numbers of Members per location     
       Jan-18Feb-18Mar-18Apr-18May-18…
      Cambridge123120126128129…
      London122127127124120…
      Oxford106104105103104…

       

      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's avatar
        Anonymous
        Not applicable
        7 years ago
        "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.