Find counts based on the very first transaction (MIN Transaction Date)

[JIGAR]

Hello Experts,

I am trying to implement a logic to calculate the distinct count of members on each state based on their first transaction within a selected date range.

I have provided the sample data and example of what I am trying to achieve.Here is the sample data.

Member Key	Transaction Date	State	Multiplier
User1	01/04/21	New Join	1
User1	02/06/21	Cancel	-1
User1	02/08/21	Reinstate	1
User2	01/10/21	New Join	1
User2	02/10/21	Cancel	-1
User3	01/04/20	New Join	1
User3	01/05/20	Cancel	-1
User3	01/12/20	Reinstate	1
User4	02/26/20	New Join	1
User5	02/22/20	New Join	1
User6	04/25/20	New Join	1
User6	04/29/20	Cancel	-1
User6	05/02/20	Reinstate	1
User7	02/10/21	Reinstate	1
User 8	02/25/20	Reinstate	1

Example :

Measure 1

If user selects a date range from Jan 1 2021 to Feb 28 2021

User 1 has two states within this date range where multiplier = 1

i.e. New Join and Reinstate on 1/4/21 and 2/8/21 respectively

I want to count User 1 for its first transaction within that period

Similarly for other users. Logic should be around first transaction where multiplier =1 in the selected time frame.

Final Result that I am looking for Measure 1 is :

New Join - 2 (User 1, User 2)

Cancel - 0

Reinstate - 1 (User 7)

Measure 2

Another measure should count the same thing but for same period last year.

So for date range from Jan 1 2020 to Feb 28 2020

Final result for Measure 2 should be

New Join - 3 ( User 3, User 4, User 5)

Reinstate - 1 (User8)

Cancel - 0

This count should adjust itself based on the selected time period.

There is one more case where if a user selects date range from Jan 1 2020 to December 31 2021 then how can we have these measures work for individual year when used on a matrix visual ?

For e.g : Between Jan 1 2020 and Dec 31 2021

Matrix should show something like this

Can someone please help me with this logic ? I would really appreciate the help.

Thank you

[V-pazhen-msft · Accepted solution]

@JIGAR 

Measure 1 = CALCULATE(COUNT('Table'[State]),FILTER('Table',[Transaction Date]=CALCULATE(MIN([Transaction Date]),ALLEXCEPT('Table','Table'[Member Key]))))

Measure 2 = CALCULATE(COUNT('Table'[State]),FILTER('Table',[Transaction Date]=CALCULATE(MAX([Transaction Date]),ALLEXCEPT('Table','Table'[Member Key]))))

Please check the pbix for detail, measure 1 is on page 1, measure 2 is on page 2.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@JIGAR , with help from a date table

Measure1 =
VAR __id = MAX ('Table'[Member Key] )
VAR __date = CALCULATE ( MAX('Table'[Transaction Date] ), ALLSELECTED ('Table' ), 'Table'[Member Key] = __id )
CALCULATE ( Count ('Table'[Transaction Date] ), VALUES ('Table'[Member Key] ),'Table'[Member Key] = __id,'Table'[Transaction Date] = __date )

Measure2 =
VAR __id = MAX ('Table'[Member Key] )
VAR __date = CALCULATE ( MAX('Table'[Transaction Date] ), ALLSELECTED ('Table' ), 'Table'[Member Key] = __id, sameperiodlastyear(Date[DATE) )
CALCULATE ( Count ('Table'[Transaction Date] ), VALUES ('Table'[Member Key] ),'Table'[Member Key] = __id,'Table'[Transaction Date] = __date, sameperiodlastyear(Date[DATE) )

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[JIGAR]

@amitchandak :

Thank you for your quick response.

These measures does not seem to be giving me the correct results. Measure 1 is showing values which is not exactly the way its expected.

Measure 1 should have give the values as 

New Join - 2 (User 1, User 2)

Cancel - 0

Reinstate - 1 (User 7)

Measure 2 shows values (incorrect result) only when the date range is selected for year 2022. However, there is no 2022 data in the table. 

Thank you