Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
@Anonymous Thank you. It has yielded the expected outcome. Highly appreciated.
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
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])
@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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
68 | |
48 | |
45 | |
18 | |
15 |