cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JaromBuys
Frequent Visitor

Count rows After specific date

I have a table of data from a call center that includes the customer number, case number and the date of the call. I have a lookup table that has the customer number and an Install date. I want to count the number of cases that were recorded After the install date.

 

Ex:

 

Cases 
CustomerCase NumberDate opened
1231089382/1/2017
1231893891/15/2017
4561778802/1/2017
4561685942/16/2017
4561698743/15/2017
   
Customer table 
CustomerInstall Date
1231/15/2017 
4562/15/2017 

 

I have tried using a simple calculate such as:

 

CaseCount = Calculate(DistinctCount(Cases[Case Number]), Filter(Cases, Cases[Date Opened] > Customer table[Install Date]))

 

Any suggestions?

2 ACCEPTED SOLUTIONS

Hi there,

 

Happy to help. So what you'll want is to use COUNTROWS as you've specified. What I'd do instead though is create two measures first. a MAX of the Install Date, and a MAX of the Open Date.

 

MAX Date/Time Opened = MAX('Dispenser Issues'[Date/Time Opened])

 

MAX Install Date 
= IF(MAX('Master Site List'[Install Dates]), MAX('Master Site List'[Install Dates]), TODAY())

I also included an IF statement in the install date where if blank, then return today's date. That way the comparison shouldn't have any false positives for comparison. The final query would be below for the count of cases where the opened date was after install date. I wrapped the final query in an X function of COUNTAX, that way the grand total will be a total count of every value that as included as part of the IF statement.

 

Case Number Count Open After Install Date = COUNTAX('Dispenser Issues', IF([MAX Date/Time Opened] > [MAX Install Date], COUNTROWS('Dispenser Issues')))

I've emailed the workbook back as well. Hope this helps! 

View solution in original post

You're definitely right. Thought too quickly and over engineered it. The solution below works fine, if you want to account for blank dates for install date you can also use the slightly modified solution as well.

 

Case Number Count Open After Install Date =
CALCULATE (
    COUNTROWS ( 'Dispenser Issues' ),
    FILTER (
        'Dispenser Issues',
        'Dispenser Issues'[Date/Time Opened]
            > IF (
                RELATED ( 'Master Site List'[Install Dates] ),
                RELATED ( 'Master Site List'[Install Dates] ),
                TODAY ()
            )
    )
)

View solution in original post

7 REPLIES 7
Vvelarde
Community Champion
Community Champion

@JaromBuys

 

Hi, try with this measure:

 

CaseCount =
COUNTROWS (
    FILTER ( 'Cases'; 'Cases'[Date opened] > RELATED ( Customer[Install Date] ) )
)

Assuming that both tables are related with Customer and Install Date is not Blank.

 

Regards

 

Victor

Lima - Peru




Lima - Peru
Anonymous
Not applicable

Hi @Vvelarde 

 

Is there a way to get the same outcome but without the tables being related?

 

I have 2 tables with dates in and want to filter table A to countrows after a date in table B. Table A & Table B are both related to the same calendar look up table and customer look up table.

 

Many thanks

James

 

Hello, thank you for your suggested solution. I tried it out and it worked fine. Unfortunately I do have blanks in my Intall Date field for customers who have not completed installation.

The solution purposed by Ried_Havens addressed this issue by adding a Max Install date that adds the current date to any blank values in this field.

You're definitely right. Thought too quickly and over engineered it. The solution below works fine, if you want to account for blank dates for install date you can also use the slightly modified solution as well.

 

Case Number Count Open After Install Date =
CALCULATE (
    COUNTROWS ( 'Dispenser Issues' ),
    FILTER (
        'Dispenser Issues',
        'Dispenser Issues'[Date/Time Opened]
            > IF (
                RELATED ( 'Master Site List'[Install Dates] ),
                RELATED ( 'Master Site List'[Install Dates] ),
                TODAY ()
            )
    )
)

Hey R


@Reid_Havens wrote:

You're definitely right. Thought too quickly and over engineered it. The solution below works fine, if you want to account for blank dates for install date you can also use the slightly modified solution as well.

 

Case Number Count Open After Install Date =
CALCULATE (
    COUNTROWS ( 'Dispenser Issues' ),
    FILTER (
        'Dispenser Issues',
        'Dispenser Issues'[Date/Time Opened]
            > IF (
                RELATED ( 'Master Site List'[Install Dates] ),
                RELATED ( 'Master Site List'[Install Dates] ),
                TODAY ()
            )
    )
)

Hey Reid,

 

I added this solution and it works as well. This seems a bit more direct than adding the two additional measures to calculate the Max Install date and Max Date/Time Opened date.

Hi there,

 

Happy to help. So what you'll want is to use COUNTROWS as you've specified. What I'd do instead though is create two measures first. a MAX of the Install Date, and a MAX of the Open Date.

 

MAX Date/Time Opened = MAX('Dispenser Issues'[Date/Time Opened])

 

MAX Install Date 
= IF(MAX('Master Site List'[Install Dates]), MAX('Master Site List'[Install Dates]), TODAY())

I also included an IF statement in the install date where if blank, then return today's date. That way the comparison shouldn't have any false positives for comparison. The final query would be below for the count of cases where the opened date was after install date. I wrapped the final query in an X function of COUNTAX, that way the grand total will be a total count of every value that as included as part of the IF statement.

 

Case Number Count Open After Install Date = COUNTAX('Dispenser Issues', IF([MAX Date/Time Opened] > [MAX Install Date], COUNTROWS('Dispenser Issues')))

I've emailed the workbook back as well. Hope this helps! 


@Reid_Havens wrote:

Hi there,

 

Happy to help. So what you'll want is to use COUNTROWS as you've specified. What I'd do instead though is create two measures first. a MAX of the Install Date, and a MAX of the Open Date.

 

MAX Date/Time Opened = MAX('Dispenser Issues'[Date/Time Opened])

 

MAX Install Date 
= IF(MAX('Master Site List'[Install Dates]), MAX('Master Site List'[Install Dates]), TODAY())

I also included an IF statement in the install date where if blank, then return today's date. That way the comparison shouldn't have any false positives for comparison. The final query would be below for the count of cases where the opened date was after install date. I wrapped the final query in an X function of COUNTAX, that way the grand total will be a total count of every value that as included as part of the IF statement.

 

Case Number Count Open After Install Date = COUNTAX('Dispenser Issues', IF([MAX Date/Time Opened] > [MAX Install Date], COUNTROWS('Dispenser Issues')))

I've emailed the workbook back as well. Hope this helps! 



Hello Reid,

 

Thank you, this modified solution worked perfectly. Because we do have blanks in the install date, we will want to take that into account.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors