topic Re: Cumulative Total of Measure New Clients in DAX Commands and Tips

Cumulative Total of Measure New Clients

VO — Thu, 23 Apr 2020 17:22:55 GMT

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

Re: Cumulative Total of Measure New Clients

Greg_Deckler — Thu, 23 Apr 2020 18:23:37 GMT

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

Re: Cumulative Total of Measure New Clients

VO — Thu, 23 Apr 2020 23:45:28 GMT

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

Re: Cumulative Total of Measure New Clients

Greg_Deckler — Fri, 24 Apr 2020 05:45:28 GMT

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])

Re: Cumulative Total of Measure New Clients

VO — Fri, 24 Apr 2020 23:17:54 GMT

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])

Re: Cumulative Total of Measure New Clients

Anonymous — Sat, 25 Apr 2020 19:25:56 GMT

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

Re: Cumulative Total of Measure New Clients

Anonymous — Sat, 25 Apr 2020 22:09:20 GMT

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

Re: Cumulative Total of Measure New Clients

VO — Sun, 26 Apr 2020 06:01:04 GMT

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