sample on date with Norm validy between two dates

[Norbertus]

Hi,

I have a two tables

1. "Norm" that have an itemname, value max, value min and startdate and enddate.

2. Samples that have a sampledate, normitem and measured value

I want to know how many samples there are in a selected period that have a value that is between the min and max value of the norm that is validity in the date of the sample

Norm

Normitem	Min	Max	StartDate	EndDate
A	5	8	01-01-2021	01-02-2021
A	5	15	01-02-2021	01-05-2021
B	20	25	01-1-2021

Sample

SampleID	Norm	Value	SampleDate
1	A	7	01-01-2021
2	A	13	15-01-2021
3	B	18	01-03-2021

Sample 1 is valid

Sample 2 is NOT valid

Sample 3 is NOT valid

Now I want a measure that counts how many samples in January 2021 are valid and NOT valid

Note: I want one measure and without using a calculated column

Can someone give me a solution or link to an information page?

Thanks

[v-eqin-msft · Accepted solution]

Hi @Norbertus ,

• Count of All matched values in 2021-Jan

Count (2021-Jan) = 
var _t=SUMMARIZE(FILTER(ALL('Sample'),[Norm]=MAX('Norm'[Normitem]) && [Value] >=MIN('Norm'[Min]) && [Value]<=MAX('Norm'[Max]) && [SampleDate] >=MAX('Norm'[StartDate]) && [SampleDate]<=MAX('Norm'[EndDate]) &&YEAR([SampleDate])=2021 && MONTH([SampleDate])=1),[SampleID])
return COUNTX(_t,[SampleID])+0

• Count of Valid:

Valid of 2021 Jan = SUMX(FILTER('Norm', [Count (2021-Jan)]>0),[Count (2021-Jan)]) 

• Count of Not Valid:

Not Valid of 2021 Jan = COUNTROWS(FILTER('Sample',YEAR([SampleDate])=2021 && MONTH([SampleDate])=1)) -[Valid of 2021 Jan]

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Norbertus , Try measure like

Measure =
var _max = maxx(allselected('Date'), 'Date'[Date])
return
maxx(Table, if( Table[Start Date] <= _max && Table[End Date] >= _max, "Valid", "Not Valid"))