Forum Discussion

JaromBuys's avatar
JaromBuys
Frequent Visitor
8 years ago
Solved

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 co...
  • Reid_Havens's avatar
    Reid_Havens
    8 years ago

    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's avatar
    Reid_Havens
    8 years ago

    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 ()
            )
    )
)
Vvelarde's avatar
Vvelarde
Community Champion
8 years ago

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

Reid_Havens's avatar
Reid_Havens
Most Valuable Professional
8 years ago

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 ()
            )
    )
)
  • JaromBuys's avatar
    JaromBuys
    Frequent Visitor
    8 years ago

    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.