cancel
Showing results for
Did you mean:  Helper IV

How to count the number of missing dates?

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

1 ACCEPTED SOLUTION  Community Champion

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.

6 REPLIES 6  Community Champion

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.  Helper IV

@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  Community Champion

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.  Helper IV

@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.  Community Champion

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.  Helper IV

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