Filter in a DAX VAR (temp)

[Anonymous]

Hi!!

I Have a problem trying of filter a temp table in a measure:

i have a database with this structure:

user	action	date	Value
1001	register	01/01/2020
1001	perfilation	01/01/2020
1001	inversion	02/01/2020	1000
1002	register	20/01/2020
1002	perfilation	25/01/2020
1003	inversion	12/01/2020	1200
1003	inversion	28/03/2020	1500

I need to create a measure for the number of users who made their first investment in a period, who also registered in that period. so, the first part of the function I already have, but I need to filter that they have been registered in the same period

New Inversions =
         VAR USERS = VALUES( Logs[Email] )
         VAR TABLE1 = CALCULATETABLE( VALUES( Logs[Email] ),
                                    ALL( Calendario[Date] ),
                                    DATESBETWEEN(Calendario[Date],[first_date],[User_start_date]))
RETURN
         COUNTROWS(
         EXCEPT( USERS,TABLE1 ) )

Additionally

first_date = first date of the database

User_start_date= start date in dashboard analysis range

[amitchandak]

@Anonymous ,

For any period you need something like this

sumx(Summarize(Table,Table[user],Table[period],"_1"
countrows(filter(Table,Table[action] in{"register","inversion"}))),if([_1]>=2,1,0))

[edhans]

Define "period." Same month, year, quarter?

[Anonymous]

The periods are months, excuse me, i forgot mention it 

[edhans · Accepted solution]

This measure will count the number of users that registered and purchased in the same month. I don't know what "inversion" and "perfilation" mean, so I used the term "inversion" and made sure there was one in a month that someone registered, because your original data didn't have that.

So this table:

will count 1 record where Inversion happened in the same month as the registration for a given user.

Registration Count = 
VAR UserPurchasePeriods =
	SELECTCOLUMNS(
		FILTER(
			Registrations,
			Registrations[action] = "inversion"
			),
			"User", Registrations[user],
			"Period", RELATED('Date'[YearMonth])
			)
VAR PurchaseInRegistrationPeriod =
	FILTER(
		Registrations,
		Registrations[action] = "register"
			&& RELATED('Date'[YearMonth]) in SELECTCOLUMNS( UserPurchasePeriods, "Period", [Period])
			&& Registrations[user] in SELECTCOLUMNS( UserPurchasePeriods, "User", [User])
			)
VAR RegistrationDate = 
	COUNTX(
		PurchaseInRegistrationPeriod,
		[Date]
		)
RETURN
RegistrationDate

See the PBIX linked here. . This needed a date table to work.