cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculate Telephone Numbers who called more than once in a month? formula needed

Wondering if you can help, in Power BI I am trying to create a formula that shows where a telephone called more than once in 30 days?

I have a table that shows following:

Originator (This is telephone)

Orginator Time (Time the call came in)

I need to see if there were any repeat callers within that 30 days and if they called more than once, highlight a value such as 1 call this month, 2 calls this month, 3 calls this month etc.

I also want to create some conditional formatting that shows where someone has called 10> times in 30 days and is highlighted as red?

Thanks,

James

3 REPLIES 3
Frequent Visitor

I am looking to show where each originator (telephone) calls more than once to highlight repeat contact within 30 days, not a overall count of calls.

Community Champion

You can create a measure in Power BI to calculate the telephone numbers that were called more than once in a 30-day period. You can also apply conditional formatting to highlight telephone numbers that were called 10 or more times in 30 days. Here's how you can do it: Create a Measure to Count Calls: Assuming you have a table with call records that includes a date and telephone number columns, you can create a measure to count the number of calls for each telephone number within the last 30 days.

CallsCountWithin30Days =

VAR CurrentDate = MAX('YourCallTable'[CallDate])

VAR ThirtyDaysAgo = CurrentDate - 30

RETURN

CALCULATE(

COUNTROWS('YourCallTable'),

FILTER(

'YourCallTable',

'YourCallTable'[CallDate] >= ThirtyDaysAgo &&

'YourCallTable'[CallDate] <= CurrentDate

)

)

1. Replace 'YourCallTable' and 'CallDate' with the actual names of your table and date column.

2. Create Conditional Formatting:

To highlight telephone numbers with 10 or more calls in the last 30 days, you can apply conditional formatting to your visual (e.g., a table or matrix). Follow these steps:

• Select the visual where you want to apply conditional formatting.
• Go to the "Format" section in the "Visualizations" pane.
• Expand the "Conditional formatting" option.
• Choose "Color scale" or "Background color" based on your preference.
• Set the rules as follows:
• Field value: Select your CallsCountWithin30Days measure.
• Minimum value: Enter 10.
• Maximum value: Enter the maximum value you expect.
• Choose the color you want for highlighting.

This will apply conditional formatting to your visual, and telephone numbers with 10 or more calls in the last 30 days will be highlighted according to your chosen color scale.

Now, your visual will show telephone numbers that were called more than once in the last 30 days, and those with 10 or more calls will be highlighted in red or the color you specified.

Frequent Visitor

Regarding Step 1 I have two fields in the table one is the originator (telephone number) the other is date call comes in as originated time, don't I need to reference the originator field so it searches for the total in 30 days? how would that look?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.