cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bourne2000
Helper IV
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

 

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

 

View solution in original post

6 REPLIES 6
bcdobbs
Community Champion
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.

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

 

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

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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors