Cumulative Total of Measure New Clients

[VO]

Definition of new cient: A client whose first transaction is in the current year (transaction date=first transaction), subsequent transactions are excluded as new.

Trying to get the cumulative of the Measure(s): NewClients

>Month Cumulative
>Year Cumulative
>LifeTimeCumulative
>% MTD vs Prior Month MTD
>% MTD vs Prior Year Similar MTD

MTD refers to Month to Date

Moreover, there is a table 'Calendar' with the column 'Calendar'[Date] with a relationship to 'Fact'[TransactionDate]. Thus, the 'Calendar'[Date] will be used in reports to get the contiguous dates.

CustomersTransacted:=DISTINCTCOUNT([CustomerID])

NewClients:=

VAR NewClientsTransacted =
CALCULATE (
[CustomersTransacted],
FILTER (
'Fact',
YEAR ( Fact[FirstTransactionDate] )
= YEAR ( MAX ( Fact[TransactionDate] ) ) && Fact[FirstTransactionDate] = Fact[TransactionDate]
)
)
RETURN
NewClientsTransacted

Table Name: Fact

CustomerID	CustomerName	TransactionDate	FirstTransactionDate
P5	D	01 March 2020	01 March 2020
P9	Q	02 March 2020	02 March 2020
P6	C	01 March 2020	01 February 2020
P8	A	01 February 2020	01 January 2020
P6	C	01 February 2020	01 February 2020
P8	A	01 January 2020	01 January 2020
P7	B	02 January 2020	02 June 2019
P4	E	01 December 2019	01 December 2019
P3	F	01 December 2019	02 June 2018
P4	E	01 November 2019	01 January 2019
P2	G	01 November 2019	01 November 2019
P1	H	01 October 2019	01 October 2019

@Anonymous 

[Anonymous · Accepted solution]

Hi there.

Please check the attached file but before you start thinking it's not doing what you wanted, please take a good look at the data the measures work with.

Best

D

[VO]

@Anonymous  Thank you. It has yielded the expected outcome. Highly appreciated.

[Greg_Deckler]

Maybe this will help: https://community.powerbi.com/t5/Quick-Measures-Gallery/New-and-Returning-Customers/m-p/168297#M13

[VO]

@Greg_Deckler 

Thank you for the suggestion. However, the [NewClients] measure in the original post already yielded the expected result. The link you have attached does not provide the solution. It is assistance on the related cumulative measures of the measure [NewClients] that is sought.

>Month Cumulative MTD
>Year Cumulative YTD
>LifeTimeCumulative
>% MTD vs Prior Month MTD
>% MTD vs Prior Year Similar MTD

MTD refers to Month to Date

[Greg_Deckler]

So then, for example for lifetime accumulated, wouldn't you just do something like:

Measure = 

  VAR __Date = MAX([Date])

  VAR __Table =

    SUMMARIZE(

      'Table',

      [Date],

      "__NewClients",[NewClients]

    )

RETURN

  SUMX(FILTER(__Table,[Date]<__Date),[__NewClients])

[VO]

@Greg_Deckler  Thank you for the suggestion. It did not give the expected result of 7, get 6 

Of greater importance would be the MTD measures

LifeTime := 

  VAR __Date = MAX([TransactionDate])

  VAR __Table =

    SUMMARIZE(

      'Fact',

      [TransactionDate],

      "__NewClients",[NewClients]

    )

RETURN

  SUMX(FILTER(__Table,[TransactionDate]<__Date),[__NewClients])

[Anonymous]

Hi there.

Your data is inconsistent.

CustomerID	CustomerName	TransactionDate	FirstTransactionDate
P1	H	01-Oct-19	01-Oct-19
P2	G	01-Nov-19	01-Nov-19
P3	F	01-Dec-19	02-Jun-18
P4	E	01-Dec-19	01-Dec-19
P4	E	01-Nov-19	01-Jan-19
P5	D	01-Mar-20	01-Mar-20
P6	C	01-Mar-20	01-Feb-20
P6	C	01-Feb-20	01-Feb-20
P7	B	02-Jan-20	02-Jun-19
P8	A	01-Feb-20	01-Jan-20
P8	A	01-Jan-20	01-Jan-20
P9	Q	02-Mar-20	02-Mar-20

Best

D