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
"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

        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

  • 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