cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
imoena
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:

 

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 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.
imoena
Frequent Visitor

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
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.
imoena
Frequent Visitor

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

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

imoena
Frequent Visitor

Hi Darek,

 

I have tested the calculation, we are getting closer 🙂

 

This is what the results show:

 Mar-19Apr-19May-19Jun-19
Cambridge120126128129
London127127124120
Oxford104105103104

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors