Count of Occurances / reoccurance

[flanshaw]

Hi - I'm sure someone can help.

I've a data set with telephone numbers and date of call. I'd like to be able to show total number of times ANY number has been called more than once in the last 30 days and the number of unique numbers. In the example below i'm after a formula for the e value of 5 (unique numbers) and 2 (re occurant..). Plan is put these as a card in PBI desktop

Telephone    number date
012-345      28/06/2017
012-456      13/06/2017
012-478       16/06/2017
012-345       02/06/2017
012-345       02/04/2017
012-329       03/03/2017
012-333        03/03/2017
012-478        01/06/2017

Total number of calls 8
Total number of unique 5
re-occrancant numbers 2

[v-jiascu-msft · Accepted solution]

Hi @flanshaw,

We need time intelligence functions here. Let's create a date table first.

Calendar =
CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) )

Then establish relationship.

Create three measures. (last 30 days)

TotalNumberOfCalls =
CALCULATE (
    COUNT ( 'Table1'[Telephone] ),
    DATESINPERIOD ( 'Calendar'[Date], TODAY (), -1, MONTH )
)

TotalNumberOfUnique =
CALCULATE (
    DISTINCTCOUNT ( Table1[Telephone] ),
    DATESINPERIOD ( 'Calendar'[Date], TODAY (), -1, MONTH )
)

ReOccrancant =
CALCULATE (
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                'Table1',
                Table1[Telephone],
                "Occurence", COUNT ( Table1[Telephone] )
            ),
            [Occurence] >= 2
        )
    ),
    DATESINPERIOD ( 'Calendar'[Date], TODAY (), -1, MONTH )
)

If you need exact 30 days or other, you could use "DATESINPERIOD" like this.

DATESINPERIOD ( 'Calendar'[Date], TODAY (), -30, DAY )

 

Best Regards!

Dale

[flanshaw]

Thanks Dale !

Works - easy when u know how