Counting number of times a datediff is over 30

[JamesMF1982]

Hi all,

I have a current measure which works out the number of days since an enquiry was entered into the system and today

CRM_OpenEnq_DaysSinceEnqLead = calculate(SUMX(ENQUIRYDETAILS,Datediff(ENQUIRYDETAILS[EnquiryBecameLeadDate],Today(),Day)),ENQUIRYDETAILS[EnquiryCurrentStageID] IN {0,1,2})

This is working on a table however, I want to have a Card that has the number of times that the results are over 30 days. 

Any help on this would be appreciated. 

James

[v-zhangti]

Hi, @JamesMF1982 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ppm1]

Please try this expression. I switched to using INT instead of DATEDIFF as it is more performant (especially since an IF would have been needed). The INT returns 1 or 0 for the T/F inside, so only the ones with >30 are counted.

Rows Over 30 =
CALCULATE (
    SUMX (
        ENQUIRYDETAILS,
        INT ( ( TODAY () - ENQUIRYDETAILS[EnquiryBecameLeadDate] ) > 30 )
    ),
    ENQUIRYDETAILS[EnquiryCurrentStageID] IN { 0, 1, 2 }
)

Pat

[amitchandak · Accepted solution]

@JamesMF1982 , Try like

CRM_OpenEnq_DaysSinceEnqLead = calculate(Countrows(Filter(ENQUIRYDETAILS,Datediff(ENQUIRYDETAILS[EnquiryBecameLeadDate],Today(),Day)>30)),ENQUIRYDETAILS[EnquiryCurrentStageID] IN {0,1,2})