How to count the number of missing dates?

[bourne2000]

Hi

I have two table called Table 1 and Table 2. Table 1 as follow, it consists of Unique subject ID, Start Date, End Date, No of days

Table 2 has  subject ID, Date

Both table has connected with subject ID and it's many to one relationship

Table 1 has Unique subject ID, Table 2 has many subject ID. In table 1, subject ID has start date and End date. In table 2, subject ID has been break down with different days. I want to calculate the number of missing days in the table 2 with compare to table 1. 

For example, If I select subject ID : 01-004, it has start date is 18.10.2014 and End date is : 31.10.2014. In Table 2, For Subject ID 01-004, it is break down from 17.10.2014 to 02.11.2014. This means, this subject ID doesn't have any missing date, so it should be zero missing days

In next example, If I select subject ID : 08-005, it has start date is 12.02.2016 and End date is : 25.02.2016. In Table 2, For Subject ID 08-005, it is break down from 12.02.2016 to 24.02.2016. This means, this subject ID has one missing date that is 25.02.2016 and so the number of missing date in the example is 1

I need to count number of missing date for all the subject ID's in the Table 2. Expecting output as follow,

Sample PBIX file here https://we.tl/t-z2Uk80trqM

Please advise

[bcdobbs]

You could add a calculated column to table 1 along the lines of...

No of Missing Dates = 
    VAR NumDates = 
        COUNTROWS (
            RELATEDTABLE ( 'Table 2' )
        )
    VAR DaysDifferent = 'Table 1'[No of days] - NumDates

    RETURN DaysDifferent

For each row of table 1 it counts how many rows are related to it in table 2 and then subtracts from the No of days you already know.

[bourne2000]

@bcdobbs Hi thanks for your reply. But it's subtracting table 2 records from no of days. I need to check the start date and end date and then cont the no of missing dates from table 2

[bcdobbs · Accepted solution]

Sorry I missed that requiement!

Is this any better:

No of Missing Dates = 
VAR NumDates = 
    COUNTROWS (
        FILTER(
            RELATEDTABLE ( 'Table 2' ),
            'Table 2'[Date] >= 'Table 1'[Start Date] 
                && 'Table 2'[Date] <= 'Table 1'[End Date]
        )
    )

VAR DaysDifferent = 'Table 1'[No of days] - NumDates

RETURN DaysDifferent

I think your existing number of days column is 1 less than it should be eg for 01-004 18-Oct to 31-Oct is 14 days not 13. If you've just subtracted the two date you need to add 1.

[bourne2000]

@bcdobbs Thanks a lot for your time. It almost near, but the values are not matching. For example, subject ID : 01-004 should be 0 but it shows 1, and subject ID : 08-005 should be 1 but it shows 0. 

Also, if you check the subject ID: 08-007 , the start date: 01.07.2016 and end date: 14.07.2016. In Table 2, if you see, date 07.07.2016 & 14.07/2016 is missing, so the number of missing date here is 2. But it shows 1.

Can you please help?

[bcdobbs]

Did you see the update I posted on my reply regarding how you're counting number of dates in your exisiting No of days column. I think that is 1 day out.

[bourne2000]

@bcdobbs Thanks a lot. I didn't notice that. I corrected the number of days