Counting the number of people who appeared more than once last month

[Anonymous]

If I have the following data (and a date table connected on the date column):

Date		Person		Appearance
01/01/2023		Jim		100
03/01/2023		Bob		101
05/01/2023		Bob		102
07/01/2023		Sally		103
12/01/2023		Colin		104
13/01/2023		Colin		105
01/02/2023		Mark		106
01/02/2023		Steve		107
01/02/2023		Mary		108
03/02/2023		Mary		109
04/02/2023		Mary		110
12/02/2023		Jane		111
15/02/2023		Daniel		112
17/02/2023		Daniel		113
21/02/2023		Sarah		114
22/02/2023		Cathy		115
23/02/2023		Cathy		116
25/02/2023		Patrick		117
03/03/2023		Will		118
11/03/2023		Will		119

I want to display on a card the number of people who appeared more than once last month. Therefore Mary, Daniel and Cathy appeared more than once in February therefore the output should be 3.

My thought is that I need to summarize the table virtually, grouping by year, month and name, counting appearances but I also think I am probably trying to overcomplicate things. 

Thanks.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thanks, the only issue with this is that it requires slicer selections rather than simply displaying the result.

[Ashish_Mathur]

Hi,

You may download the revised PBI file from here.

Hope this helps.

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26Rhj-e3CpxoSjriSys?e=L2O00c

[Anonymous]

Thanks, very similar to my effort but the way you have gotten the date is a little more elegant.

I have a question though... you have chosen to use the ADDCOLUMNS function to count the occurrences rather than adding it as part of the SUMMARIZE function. The following appears to provide the same result:

is their a reason why doing this via ADDCOLUMNS is better? or is it simply an alternative?

Thanks.

[Ahmedx]

read about it here:
https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

[Anonymous]

Thank you, one final question if you don't mind... on line 10 you are referring to the 'Count' measure you created in step one. Is this simply for clarity or it more advantageous compared with replacing your [Count] with COUNT[Person], having the calculation all within one measure?

Edit: actually changing it no longer gives me the correct answer...

[Anonymous]

So I think I have a solution but I am sure there is a better way to do this with time intelligence, variables used to ensure I am selecting the right month from the right year:

Measure =

var _previousmonth = MONTH(EOMONTH(TODAY(), -2)+1)

var _year = IF(_previousmonth = 12, YEAR(TODAY())-1, YEAR(TODAY()))

RETURN

COUNTROWS(FILTER(SUMMARIZECOLUMNS('Date'[Year], 'Date'[MonthOfYear], Table[Person], "@Count", COUNT(Table[Appearance])), [@Count] > 1 && [Year] = _year && [MonthOfYear] = _previousmonth )

Can anyone point out a flaw in the above or a better way to do this with time intelligence? (or just a better way)

[amitchandak]

@Anonymous , First create a measure like this with help form date table and TI

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Then a measure like

Countx(values(Table[Person]), if([Last Month Sales]>1, [Person], Blank())

[Anonymous]

Sorry I'm not really following this, I have the following:

I want to display on a card the number of people who appeared more than once last month. Therefore Mary, Daniel and Cathy appeared more than once in February therefore the output today, in March, should be 3. The card should update each month, so in February the card would read 2 as Colin and Bob both appeared more than once in January.