Need Help Writing A Formula

---

v-joesh-msft wrote:

Hi Anonymous ,

Based on your sample data, I replaced some of the Name fields with some English words. I did some tests and the results are as follows, hope is the output you expect:

1. delete or remove any name that has not called in the last three(3) consecutive months from the table

Need to create two measures:

Last three month =
VAR _LASTDATE =
    CALCULATE ( MAX ( 'Data SUV'[Date] ), ALL ( 'Data SUV' ) )
RETURN
    VAR _VALUE =
        CALCULATE (
            COUNTROWS ( 'Data SUV' ),
            FILTER (
                'Data SUV',
                DATEDIFF ( 'Data SUV'[Date], _LASTDATE, MONTH ) >= 0
                    && DATEDIFF ( 'Data SUV'[Date], _LASTDATE, MONTH ) < 3
            )
        )
    RETURN
        _VALUE

Remove not called =
VAR _TABLE =
    CALCULATETABLE (
        SUMMARIZE (
            'Data SUV',
            'Data SUV'[Name],
            'Data SUV'[Date].[Date],
            "_VALUE", [last three month]
        ),
        ALLEXCEPT ( 'Data SUV', 'Data SUV'[Name] )
    )
RETURN
    IF (
        COUNTAX ( FILTER ( _TABLE, [_VALUE] <> BLANK () ), [_VALUE] ) = 3,
        COUNTROWS ( 'Data SUV' )
    )

The figure below is the result comparison：

2. show the top 10 highest callers at a glance

Need to create two measures:

Rank =
IF (
    [remove not called] <> BLANK (),
    RANKX ( ALL ( 'Data SUV'[Name] ), [remove not called],, DESC )
)

top10 =
CALCULATE (
    [remove not called],
    FILTER ( VALUES ( 'Data SUV'[Name] ), [Rank] <= 10 )
)

(The figure below shows only 3 rankings because I filled in fewer name fields)

 

Here is a demo, please try it：

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ER21DiymeMBPlA86AUXA2-oBz6h3HfV1T-Ts_Gmh45KseQ?e=RBTqfn

 

Best Regards,

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

---

v-joesh-msft  Hi Friend, Many thanks for taking the time to help me through this sample. You got the concept correctly. Based on the results you tested and my own testing, I think I might need some mofidications with the formula.

From the picture, it seems to remove names that did not call in one month or the previous month. This I think did not cpature the 3 consecutive months concept. So for example, if a NAME did not call JULY, AUGUST & SEPTEMBER then that name should be removed. I am looking to identify high consistent callers for my department.

The top 10 callers was spot on but it seems to be based on the previous formula.

Thanks for your help and looking forward to your reply.