Forum Discussion
Count items in table A if filter date from table B falls between start & end date from table B
How to count the distinct serial number in table A if filter date from table B falls between start & end date from table B? I want to calculate the total count of serial number of Table B + count of...
Hi Anonymous ,
Please try this:
Serial Number in TA 1 = VAR t = FILTER ( TableA, TableA[Start Date] <= MIN ( 'Created date from table B'[Created Date] ) && TableA[End Date] >= MAX ( 'Created date from table B'[Created Date] ) ) RETURN COUNTROWS ( t )Serial Number in TB 1 = VAR t = FILTER ( TableA, TableA[Start Date] <= MIN ( 'Created date from table B'[Created Date] ) && TableA[End Date] >= MAX ( 'Created date from table B'[Created Date] ) ) RETURN COUNTROWS ( FILTER ( t, [Serial Number] IN VALUES ( TableB[Serial Number] ) ) )
v-xuding-msft ,thanks!
How about multiple selection of created date?
It only works for single selection now but i need date range selection for created date.
Thanks.
v-xuding-msft
Community Support
Community SupportHi Anonymous ,
Please try this:
Serial Number in TA 1 =
VAR t =
FILTER (
TableA,
TableA[Start Date] <= MIN ( 'Created date from table B'[Created Date] )
&& TableA[End Date] >= MAX ( 'Created date from table B'[Created Date] )
)
RETURN
COUNTROWS ( t )
Serial Number in TB 1 =
VAR t =
FILTER (
TableA,
TableA[Start Date] <= MIN ( 'Created date from table B'[Created Date] )
&& TableA[End Date] >= MAX ( 'Created date from table B'[Created Date] )
)
RETURN
COUNTROWS ( FILTER ( t, [Serial Number] IN VALUES ( TableB[Serial Number] ) ) )
Thanks v-xuding-msft !! it works!
Hi v-xuding-msft,
How do I calculate the distinct count of Serial number which their start and end date has touch the Created Date range I selected?
Below is the example, E and F should be excluded from the count.