Count items in table A if filter date from table B falls between start & end date from table B

[PBI_newuser]

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 serial number of Table A (fall between the selected slicer Created Date) excluding serial number count of Table B.

Please help!!!! Thanks.

For example:
If I select slicer "Created Date" = 10/1/2019 to 4/1/2020.

Count of Serial Number in Table B= 5

Count of Serial Number in Table A

= 3 (Start & End Date within "Created Date") - 2 (appear in Table 2) = 1

Table A:

Type	Serial Number	Start Date	End Date
A	A-123	5/1/2019	4/30/2020
A	B-123	7/1/2019	6/30/2020
A	C-123	2/1/2018	1/31/2019
A	D-123	8/1/2019	7/31/2020
A	E-123	4/1/2018	3/31/2019

Table B:

Type	Serial Number	Created Date
A	B-123	1/24/2020
A	A-123	8/13/2019
A	H-123	4/1/2020
B	X-321	12/23/2019
B	Z-321	10/12/2019

[v-xuding-msft]

Hi @PBI_newuser ,

Please try this:

Created date from table B = VALUES(TableB[Created Date])

selected date = SELECTEDVALUE('Created date from table B'[Created Date])

Serial Number in TA = 
var t = FILTER(TableA,TableA[Start Date]<= [selected date] && TableA[End Date] >= [selected date])
return
COUNTROWS(t)

Serial Number in TB = 
var t = FILTER(TableA,TableA[Start Date]<= [selected date] && TableA[End Date] >= [selected date])
return
COUNTROWS(FILTER(t,[Serial Number] in VALUES(TableB[Serial Number])))

[PBI_newuser]

@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 · Accepted solution]

Hi @PBI_newuser ,

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] ) ) )

[PBI_newuser]

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.

[PBI_newuser]

Thanks @v-xuding-msft !! it works!

[amitchandak]

@PBI_newuser , create a date table join it with the start date of Table A and created Date of Table 2. Use the slicer from Date Table , then it should

count(TableA[Serial Number]) -count(TableB[Serial Number])

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[PBI_newuser]

Hi @amitchandak, thanks for your reply.
How can I count the serial number in Table A if the selected date range in Table B falls between Start & End date of serial number in Table B?

If I create a Date table to join with start date in Table A, how if the start date is earlier than the created date (Table B) but end date is before the created date?

Thanks.