Forum Discussion

DBekkerry's avatar
DBekkerry
Frequent Visitor
3 years ago
Solved

Count unique values within range of time

Hi everyone. 

 

I hope you can help me. I have read almost every other post with similar description, seeing if it could solve my problem but unfortunately no.

 

I have a set of phone numbers and a timestamp. 

I would like to know how often a unique number appears, within 48 hours of the timestamp - before and after. 

 

TIME  PHONENUMBER
17-01-2023 12:23              A
16-01-2023 12:36              A
14-01-2023 16:08              A
11-01-2023 12:09              B
03-01-2023 15:27              C
02-01-2023 09:26              C
15-11-2022 12:30              C
14-11-2022 09:40              D
13-11-2022 15:40              C

The output that I am looking for is something like this:

 

TIMEPHONENUMBEROccurrences
17-01-2023 12:23              A          2
16-01-2023 12:36              A          3
14-01-2023 16:08              A          2
11-01-2023 12:09              B          1
03-01-2023 15:27              C          2
02-01-2023 09:26              C          2
15-11-2022 12:30              C          2
14-11-2022 09:40              D          1
13-11-2022 15:40              C          2

 

Can anybody out there help me

Need Help
  • DBekkerry's avatar
    DBekkerry
    3 years ago

    Found a solution. Instead of deleting, I hope somone can use it in the future: 

    Calculation =
    VAR twodaysafter = [DATECOLUMN] + 2
    VAR twodaysbefore = [DATECOLUMN] - 2
    VAR Tlfnr = [Phonenumber]
     
    RETURN
    CALCULATE (
        COUNTROWS ('Table'),
        ALL ('Table'),
        'FCR Kald Privat'[SOURCE_ADDRESS] = Phonenumber,
        'FCR Kald Privat'[FCR DATO] <= twodaysafter,
        'FCR Kald Privat'[FCR DATO] >= twodaysbefore 
    )

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi!

     

    Select count from the drop down in the visualisations pane. here's a screenshot! 🙂 please accept as solution if this works

  • DBekkerry's avatar
    DBekkerry
    Frequent Visitor
    3 years ago

    Found a solution. Instead of deleting, I hope somone can use it in the future: 

    Calculation =
    VAR twodaysafter = [DATECOLUMN] + 2
    VAR twodaysbefore = [DATECOLUMN] - 2
    VAR Tlfnr = [Phonenumber]
     
    RETURN
    CALCULATE (
        COUNTROWS ('Table'),
        ALL ('Table'),
        'FCR Kald Privat'[SOURCE_ADDRESS] = Phonenumber,
        'FCR Kald Privat'[FCR DATO] <= twodaysafter,
        'FCR Kald Privat'[FCR DATO] >= twodaysbefore 
    )