cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bourne2000
Helper V
Helper V

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

 

bourne2000_0-1638690798842.png

 

Table 2 has  subject ID, Date

 

bourne2000_1-1638690851473.png

 

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

bourne2000_2-1638690923673.png

 

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

 

 

bourne2000_3-1638691073522.png

 

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,

 

bourne2000_4-1638691594947.png

 

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

 

Please advise

 

1 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.

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

6 REPLIES 6
bcdobbs
Super User
Super User

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

@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

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.

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

@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?

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!