Measure that includes only first entries in a time frame to calculations

[Anonymous]

Hi!

I'm trying to calculate distinctcount from products tested in certain period of time. I got [SerialNumber]-column to identify exact product. I should count only those tests that are new for the time period (Test can fail and the same serial number will be tested again, but I don't want to count Re-tests).

I'd like to find a Dax measure that checks when the first test of a serial number took place and excludes those from the count if they won't fit the time frame. 

I'm using separate calender table to get the time intelligence right. It's linked to [LocalDate]-column included in TestResults-table. TestResults-table is in import-mode if that helps with formulating the DAX.

I've made the measure which returns corrct date for first entry of a serial number, but when filtering the visuals, It still counts those re-tests in.
Picture below shows my problem. As you can see FirstPVM on the left says Serial number with 1000 is tested first time in 23.6. but still on the right side graph it still shows up on the 24.6 . Description on the right side says basically OK(re-tested) for the 1000 SN.

Method for retrieving the first test date is following:

FirstPVM =

CALCULATE(

FIRSTDATE(TestResults[LocalDate]);

FILTERS(TestResults[SerialNumber])

)

Measure for counting all new products is following:

AllTestedProducts =

CALCULATE(

DISTINCTCOUNT(TestResults[SerialNumber]);

FILTER(TestResults;TestResults[LocalDate]=[FirstPVM]);

FILTERS(TestResults[Team])

)

I'd like to modify it so the other dax measures won't calculate those serial numbers in which are first tested earlier than time frame (In example above before 24.6.). Also it should keep the original first date on FIrstPVM regardless slicer filtering.

Only first tests of time frame should be counted. For example if I take last week, it should only count those serial numbers which has first entry in that week.

I'm happy to answer your questions, because I'm not sure if this was clear enough 😄

Thanks for help in advance!

[Anonymous · Accepted solution]

// T is your table with tests.

[Absolute First Test Date] =
// This does not observe any
// filters on dates. Just returns
// the very first test date throughout
// the whole data set.
IF( HASONEFILTER( T[SerialNumber] ),
	var __serial = SELECTEDVALUE( T[SerialNumber] )
	var __firstTestDate =
		MINX(
			CALCULATETABLE(
				T,
				T[SerialNumber] = __serial,
				ALL( T )
			),
			T[TestDate]
		)
	return
		__firstTestDate
)

[Is First Test Date in Timeframe] =
// Returns TRUE if for the current SN
// its absolute first test date is in
// the selected period of time. If not,
// returns FALSE and returns BLANK if
// more than 1 SN is being actively filtered.
IF( HASONEFILTER( T[SerialNumber] ),
	[Absolute First Test Date]
		IN VALUES( Calendar[Date] )
)


[# SNs with First Tests in Timeframe] =
// Gets the number of SNs that have
// their very first test date in the
// selected time frame.
var __result =
	SUMX(
		VALUES( T[SerialNumber] ),
		1 * [Is First Test Date in Timeframe]
	)
return
	IF( __result > 0, __result )

[lbendlin]

Watch out for your filter context. Use ALLSELECTED() or KEEPFILTERS()

[Anonymous]

How does it works in this case? Where should I put it? on FirstPVM-measure or AllTestedProducts?