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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

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

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

       

      Thanks.

       

      Best

      Darek