cancel
Showing results for
Search instead for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Counting number of times a datediff is over 30

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

1 ACCEPTED SOLUTION
Super User

@JamesMF1982 , Try like

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

3 REPLIES 3
Community Support

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.

Solution Sage

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

Microsoft Employee
Super User

@JamesMF1982 , Try like

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

## Helpful resources

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors