Calculated column that tells me whether someone has engaged with organisation repeatedly

[MadalenaBicho]

Hi all,

I am very new to Power BI but I am currently working on a Power BI report for my organisation. I have a table that looks like the below: 

Name	Month	Number of engagements
Jane Doe	July 2020	3
Jane Doe	August 2020	6
John Doe	July 2020	10
John Doe	August 2020	2
John Doe	November 2020	7

We have a category of client called 'recurrent' which we classify as those that have engaged with us 3 or more months out of the past 12 months. I would like my report to be able to automatically identify these clients by using a measure or a calculated column. I was thinking of a calculated column with a DAX formula that returns True or False results based on the criteria I've specified. Therefore, Jane Doe would return False but John Doe would return True. 

However, I'm very new to DAX and Power BI in general and so would appreciate some help on how to achieve this. 

Many thanks in advance!!!

[Anonymous · Accepted solution]

Hi, @Gabry 

Your thinking is correct. 

And I would like to share some additional solutions below. 

Hi, @MadalenaBicho 

I am glad to help you. 

Based on your description, I'm assuming you mean that clients who have worked with you for 3 months or more in the last 12 months are referred to as 'recurrent'? 

If I understand you correctly, perhaps the test dataset you are giving is not entirely appropriate as your dataset is more than 12 months old for today.

Therefore I have added a few more rows of test data based on your data: 

You can then add a new calculated column in Power BI Desktop by clicking on New Column: 

Recurrent = 
VAR CurrentDate =
    MAX ( 'Table'[Month] )
VAR StartDate =
    EDATE ( CurrentDate, -12 )
VAR EngagementCount =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Month] ),
        FILTER (
            'Table',
            'Table'[Name] = EARLIER ( 'Table'[Name] )
                && 'Table'[Month] >= StartDate
                && 'Table'[Month] <= CurrentDate
        )
    )
RETURN
    IF ( EngagementCount >= 3, TRUE, FALSE )

In my data, only 'Mike Green' is a match, so only his row returns 'True': 

Hope this helps! 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gabry]

Hello @MadalenaBicho ,

the solution may vary based on your model.

As a fast solution to your question I can tell:

CalculatedColumn= 
Var _user= [name]
var _numM= calculate(distinctcount([month]), all(table), 'table'[name]=_user)
return

if(_numM>=3, 1,0)

Let me know if it's ok